Monthly Archives: June, 2016

Query to find out EUL5 tables that should have been dropped!

Whenever we schedule any DISCO report, there is a default expiry for report results. When such scheduled reports run, the results are stored in EUL5_B result tables.

As per the process, these result tables should be dropped after the expiry. However, in few unknown cases, these results tables are still available in the database after expiry. The below query will help us identify such tables.

select ‘drop table ‘||BQT_TABLE_NAME||’ cascade constriants;’
from (
select to_date(substr(LTRIM(BQT.BQT_TABLE_NAME,’EUL5_B’),3,2)||’/’|| substr(LTRIM(BQT.BQT_TABLE_NAME,’EUL5_B’),5,2)||’/’||’20’|| substr(LTRIM(BQT.BQT_TABLE_NAME, ‘EUL5_B’),1,2),’mm/dd/yyyy’) results_date
,trunc(sysdate-br_expiry) results_expired_date
,BQT.BQT_TABLE_NAME,FU.USER_NAME, BQT.BQT_CREATED_DATE,
BRR.BRR_CREATED_DATE, BRR.BRR_RUN_DATE,
BR.BR_NEXT_RUN_DATE, BR.BR_AUTO_REFRESH, BR.BR_EXPIRY, BR.BR_NAME, BR.BR_WORKBOOK_NAME
FROM
disceul_us.eul5_bq_tables BQT
JOIN DISCEUL_US.EUL5_BR_RUNS BRR ON BQT.BQT_BRR_ID = BRR.BRR_ID
JOIN DISCEUL_US.EUL5_BATCH_REPORTS BR ON BRR.BRR_BR_ID = BR.BR_ID
JOIN DISCEUL_US.EUL5_EUL_USERS EU ON BR.BR_EU_ID = EU.EU_ID
LEFT JOIN APPS.FND_USER FU ON REPLACE(EU.EU_USERNAME,’#’,”) = FU.USER_ID
);

If any quotes alignment issues from copying above, alternatively you can download query from below link:

EUL5_drop_tables_after_expiry

 

Advertisements

Map EUL5 result tables with username

The below query will help to map between EUL5 result tables, report name, and the user who ran it.

***

WITH APPS_SEG_INFO AS
(
SELECT /*+ PARALLEL(8) */ SEGMENT_NAME, SUM(BLOCKS)*8/1024 BQT_TABLE_SIZE_MB FROM dba_segments
where OWNER = ‘APPS’ AND SEGMENT_NAME like ‘%EUL5_B%R%’ GROUP BY SEGMENT_NAME
)
select
BQT.BQT_TABLE_NAME “TABLE_NAME(BQT)”, ASI.BQT_TABLE_SIZE_MB, BQT.BQT_CREATED_DATE “TABLE_CREATED_DATE(BQT)”,
BRR.BRR_CREATED_DATE “TABLE_CREATED_DATE(BRR)”, BRR.BRR_RUN_DATE “SCHEDULE_RUN_DATE(BRR)”,
BR.BR_NEXT_RUN_DATE, BR.BR_AUTO_REFRESH, BR.BR_EXPIRY, BR.BR_NAME, BR.BR_WORKBOOK_NAME, FU.USER_NAME
–BQT.BQT_ID, BRR.BRR_ID, BR.BR_ID, BRR.BRR_CREATED_DATE, BRR.BRR_RUN_DATE, BRR.BRR_STATE,
–BQT.BQT_TABLE_NAME, BQT.BQT_CREATED_DATE, BR.BR_NAME, BR.BR_WORKBOOK_NAME, BR.BR_EXPIRY, BR.BR_CREATED_BY, BR.BR_EU_ID,
–EU.EU_USERNAME, FU.USER_NAME, ASI.BQT_TABLE_SIZE_MB
FROM
disceul_us.eul5_bq_tables BQT
JOIN DISCEUL_US.EUL5_BR_RUNS BRR ON BQT.BQT_BRR_ID = BRR.BRR_ID
JOIN DISCEUL_US.EUL5_BATCH_REPORTS BR ON BRR.BRR_BR_ID = BR.BR_ID
JOIN DISCEUL_US.EUL5_EUL_USERS EU ON BR.BR_EU_ID = EU.EU_ID
LEFT JOIN APPS.FND_USER FU ON REPLACE(EU.EU_USERNAME,’#’,”) = FU.USER_ID
JOIN APPS_SEG_INFO ASI ON BQT.BQT_TABLE_NAME = ASI.SEGMENT_NAME
ORDER BY ASI.BQT_TABLE_SIZE_MB DESC
;

***

Query: Auto Refresh DISCO reports details

select FU.USER_NAME, FU.description,
BR.BR_CREATED_DATE,BR.BR_NEXT_RUN_DATE,
‘Every ‘ || br.br_num_freq_units || ‘ ‘ ||
DECODE(br.br_rfu_id,
2000,’Minutes’,
2001,’Hours’,
2002,’Days’,
2003,’Weeks’,
2004,’Months’,’Years’) “Run Frequency”,
BR.BR_WORKBOOK_NAME, BR.BR_NUM_FREQ_UNITS,BR.BR_RFU_ID,
BR.* from
DISCEUL_US.EUL5_BATCH_REPORTS BR, APPS.FND_USER FU
where
BR.BR_AUTO_REFRESH = 1
AND replace(BR.BR_CREATED_BY, ‘#’,”) = FU.USER_ID
ORDER BY USER_NAME;

 

(Script) db2 force applications for a database

$ cat db_force.sh
#!/bin/bash
##This script takes database name as argument
##and displays db2 force application commands for given database
##You don’t have to run “Force application All” just to
##terminate a specific database connections.

if [ “$#” -ne “1” ]
then
echo “USAGE:$0  <Database Name>”
exit;
fi
#DBNAME=`echo $1 | tr a-z A-Z`
db2 list applications | awk -v DBNAME=`echo $1 | tr a-z A-Z` -F” ” ‘{if ($5 == DBNAME) print “db2 \”force application(“,$3,”)\”” }’
if [ “$?” -ne “0” ]
then
echo “Exit code is: Failure”
exit
fi

Alternatively, the script can be downloadable from below link:

db_force.sh

 
Example usage of script:
************************

db_force_screenshot1

 

DB2 LUW: Table size

We can use below query to find out object’s physical size:

Query to give table size for T1 table in DB2INST1 schema:
******
db2 => select data_object_p_size, available from table(sysproc.admin_get_tab_info_v97(‘DB2INST1’, ‘T1’)) AS T

DATA_OBJECT_P_SIZE AVAILABLE
——————– ———
384                                            Y

1 record(s) selected.

 
Physical size meaning – Amount of disk space physically allocated for the table in Kilobytes.

The space reported by (DATA_OBJECT_P_SIZE) represents the physical size of the base table only. Space consumed by LOB data, Long Data, Indexes and XML objects are reported by other columns such as INDEX_OBJECT_P_SIZE, LONG_OBJECT_P_SIZE, XML_OBJECT_P_SIZE.

AVAILABLE: This columns specifies the state of the table.
N: Table is unavailable. If the table is unavailable, all other output columns relating to the size and state will be NULL.
Y: table is available.

db2 9.1 compatibility on AIX 7.1

The following DB2 LUW versions support the AIX 7.1 operating system:

  • DB2 Version 9.1
  • DB2 Version 9.5
  • DB2 Version 9.7
  • DB2 Version 9.8 Fix Pack 3 or later

If you plan to install DB2 LUW products on AIX 7.1 operating systems and are using the DB2 High Availability Feature, you must ensure that the following minimum DB2 fix pack levels are installed:

  • DB2 Version 9.1 Fix Pack 10
  • DB2 Version 9.5 Fix Pack 6a
  • DB2 Version 9.7 Fix Pack 3
  • DB2 Version 9.8 Fix Pack 3

Ref: http://www-01.ibm.com/support/docview.wss?uid=swg21456970

IBM Netezza DBA Training Exercises

Exercise1 – create database and check metadata location

Exercise2 – Password Cache

Exercise3 – Multi Schema Support and Bounce Netezza Instance – Copy

Exercise4 – Database Objects Creation

Exercise5 – Distributions

Exercise6 – Data Loading Unloading using External tables

Exercise7 – Database loading using NZLOAD

Exercise8 – Backup and Restore

 

 

DB2 LUW: Import & Export example

Little background on data movement utilities:

Three major data movement utilities are available in DB2 LUW:

  • Export
  • Import
  • Load

These data movement utilities support various file formats:

  • Non-delimited or fixed length ASCII (ASC)
  • Delimited ASCII (DEL)
  • PC Version of the Integrated Exchange Format (PC/IXF)
  • Worksheet Format (WSF)
  • Cursor

ASC:
This file type contains ASCII data in fixed length to align with column data.
Each ASC file is a stream of ASCII characters consisting of data values ordered by row and column. Rows are separated by row delimiters, which are assumed to be newline characters.

DEL:
Most common file format used by a variety of database managers for data exchange
It contains ASCII data and uses special character delimiters to separate column values.
Rows in the data stream are separated by a newline character as the row delimiter.

PC/IXF:
This is a structured description of a database table. We will see this with example in the coming slides.
This file format can be used not only to import data but also to create a table that does not already exist in the target database.

WSF:
Data stored in this format can be interpreted in worksheets. It can be used for export and import only.

Cursor:
A cursor is declared with a query. It can only be used as the input of a load operation. We will see this with example in the coming slides.

The below steps explain the process of Import and Export in DEL format:

Step1: Create a database, table and data file

-bash-4.1$ db2 “CREATE DATABASE testdb”
DB20000I The CREATE DATABASE command completed successfully.

-bash-4.1$ db2 “CONNECT TO testdb”

Database Connection Information

Database server = DB2/LINUXX8664 10.5.7
SQL authorization ID = DB2INST1
Local database alias = TESTDB

-bash-4.1$ db2 “CREATE TABLE customer(cid INTEGER, cname VARCHAR(20), cage SMALLINT, cgender CHAR(1), caddress VARCHAR(50))”
DB20000I The SQL command completed successfully.

-bash-4.1$ cat customer.del (Note: extension of file can be anything!)

501|Ravi Nandigam|32|M|India
502|David Oklay|44|M|Ireland
503|Elmer Elandy|35|M|Canada
504|Prasanna A|24|F|India
505|Susan Mathew|56|F|USA
506|Michael Tango|30|M|Nigeria
507|Betty Lee|28|F|USA
508|Jennifer Lewis|12|F|UK
509|Sarah Thompson|89|F|UK
510|Nitin Joseph|22|M|India

Note: here column delimiter is ‘|’. You have to specify the same with option “coldel” while importing

Step2: Import above 10 rows in customer table

-bash-4.1$ db2 “IMPORT FROM /mnt/db2home/scripts/customer.del of DEL MODIFIED BY coldel| MESSAGES /mnt/db2home/scripts/customer_imp.msg INSERT INTO
db2inst1.customer”

Number of rows read = 10
Number of rows skipped = 0
Number of rows inserted = 10
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 10

-bash-4.1$ db2 “select count(*) from db2inst1.customer”

1
———–
10

1 record(s) selected.

Note: If any error/warning during process, it will write in customer_imp.msg file

Step3: Export customer table in DEL format

-bash-4.1$ db2 “connect to testdb”

Database Connection Information

Database server = DB2/LINUXX8664 10.5.7
SQL authorization ID = DB2INST1
Local database alias = TESTDB

-bash-4.1$ db2 “insert into db2inst1.customer values (511, ‘Andrew Roy’, 31, ‘M’, ‘Canada’)”
DB20000I The SQL command completed successfully.

-bash-4.1$ db2 “select count(*) from db2inst1.customer”

1
———–
11

1 record(s) selected.
-bash-4.1$ db2 “export to /mnt/db2home/scripts/customer_11.del OF DEL MODIFIED BY coldel| MESSAGES /mnt/db2home/scripts/customer_11.msg SELECT * FROM
db2inst1.customer”

Number of rows exported: 11

-bash-4.1$ cat /mnt/db2home/scripts/customer_11.del
501|”Ravi Nandigam”|32|”M”|”India”
502|”David Oklay”|44|”M”|”Ireland”
503|”Elmer Elandy”|35|”M”|”Canada”
504|”Prasanna A”|24|”F”|”India”
505|”Susan Mathew”|56|”F”|”USA”
506|”Michael Tango”|30|”M”|”Nigeria”
507|”Betty Lee”|28|”F”|”USA”
508|”Jennifer Lewis”|12|”F”|”UK”
509|”Sarah Thompson”|89|”F”|”UK”
510|”Nitin Joseph”|22|”M”|”India”
511|”Andrew Roy”|31|”M”|”Canada”

Note: OF DEL tell the export utility to export data in Delimited ASCII format. OF IXF tells to export the data in binary format

Feel free to use comments section for any queries/suggestions.

Cheers!
Ravi

Netezza: Multiple history databases

(Q) Can we have multiple history databases created?

An history database captures information about user activity such as

  • Queries
  • Query plans
  • Table access
  • column access
  • session creation
  • and failed authentication.

Trust me, your auditors love this information 🙂

The above information is stored in a database that you have created using nzhistcreatedb command.

Once you create database(s), you have to create an history configuration using “CREATE HISTORY CONFIGURATION” to decide on what history to collect and at what frequency and what database to load this information.

We can create N number of history databases but only one can be active at any time. Whichever history configuration is active, that decides the current history database.

Cheers!

DB2 LUW: Modify DIAGSIZE (or) Set up rotational diagnostic log

Rotating dignostic and administration notification logs (DIAGSIZE > 0) was introduced in DB2 LUW 9.7. This is not a dynamic parameter; the new value/behaviour will take effect only after an instance restart.

Below are the steps we need to follow to change this instance level parameter:

Step1: diagsize is an instance level parameter
db2 get dbm cfg | grep -i ‘DIAGSIZE’

step2: to change DIAGSIZE from “0” to “200 MB”
db2 update dbm cfg using diagsize 200

Step3: Bounce instance
Note1:
There are two types of Diagnostic logs:
(a) db2<instance_name>.nfy => For use by database and system administrators
(b) db2diag.log => For use by IBM DB2 Support

Note2:
The total size of the db2diag.N.log and db2<instance_name>.N.nfy files will not exceed the DIAGSIZE value in MB.

Note3:
The partition ratio between the db2diag.N.log files and the db2<instance_name>.N.nfy files is approximately 90% to 10%. 90% of the DIAGSIZE value is assigned to the db2diag.N.log files and 10% to the db2<instance_name>.N.nfy files.
Cheers!