Search

Ramchandra's Weblog

Just ……everything about DB2 LUW

Category

DB2 Articles

Script to Reset Identity Columns

db2 connect to <database>
db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
db2 “select ‘select ”ALTER TABLE ‘|| rtrim(TBCREATOR) ||’.’||TBNAME ||’ ALTER COLUMN ‘||NAME ||’ RESTART WITH ”’ ||’||cast( max(‘||NAME||’)+1 as char(15)) ||”;” from ‘|| rtrim(TBCREATOR) ||’.’||TBNAME ||’;’ from   SYSIBM.SYSCOLUMNS     where IDENTITY=’Y'” > temp_identity1.sql
db2 -tvf temp_identity1.sql|grep ‘ALTER TABLE’  >  temp_identity2.sql
cat temp_identity2.sql|grep ‘;’$ > temp_identity3.sql
db2 -tvf temp_identity3.sql > temp_identity4.sql
db2 UNQUIESCE DATABASE

DB2 Viper 2 compatibility features

DB2 Viper 2 compatibility features

DB2 Viper 2 compatibility features

Determining performance problems with DB2 Java applications

Determining performance problems with DB2 Java applications

Determining performance problems with DB2 Java applications

Converting non-Unicode databases to Unicode

Converting non-Unicode databases to Unicode

There are some cases where you might need to convert an existing non-Unicode database to a Unicode database.
Before you begin
You must have enough free disk space to export the data from the non-Unicode database. Also, if you are not reusing the existing table spaces, you will need enough free disk space to create new table spaces for the data.
About this task
The following steps illustrate how to convert an existing non-Unicode database to a Unicode database:

1. Export your data using the db2move command:

cd <export-dir>
db2move sample export

where is the directory to which you want to export your data and SAMPLE is the existing database name.
2. Generate a DDL script for your existing database using the db2look command:

db2look -d sample -e -o unidb.ddl -l -x -f

where SAMPLE is the existing database name and unidb.ddl is the file name for the generated DDL script. The -l option generates DDL for user defined table spaces, database partition groups and buffer pools, the -x option generates authorization DDL, and the -f option generates an update command for database configuration parameters.
3. Create the Unicode database:

CREATE DATABASE UNIDB COLLATE USING SYSTEM_codepage_territory

where UNIDB is the name of the Unicode database and SYSTEM_codepage_territory is a language-aware collation based on the weight table used for collating your non-Unicode data. This ensures that the data in the new Unicode database will be sorted in the same order.
4. Edit the unidb.ddl script:
1. Change all occurrences of the database name to the new Unicode database name:

CONNECT TO UNIDB

2. Increase the column lengths for character columns in your tables. When characters are converted to Unicode, there may be an expansion in the number of bytes. It is recommended that you increase the length of the character columns to compensate for this expansion.
3. To keep the existing database, you must also change the file name specification for table spaces in the unidb.ddl file. Otherwise, you can drop the existing database and use the same table space files:

DROP DATABASE SAMPLE

5. Recreate your database structure by running the DDL script that you edited:

db2 -tvf unidb.ddl

6. Import your data into the new Unicode database using the db2move command:

cd <export-dir>
db2move unidb import

where is the directory where you exported your data and UNIDB is the Unicode database name.

DB2 9.5 Upgrade Benefits & Best Practices (V2) – ChannelDB2

DB2 9.5 Upgrade Benefits & Best Practices (V2) – ChannelDB2

DB2 ROWS TO COLUMN

CREATE A TABLE AS BELOW

create table PIVOT(
    ID INTEGER,
    NAME  CHAR(10),
    COLCOUNT  INTEGER
)

INSERT THE VALUES AS BELOW 

SELECT * FROM PIVOT

/*
ID    NAME    COLCOUNT
1    COL101        1
1    COL102        2
1    COL103        3
2    COL201        1
2    COL202        2
2    COL203        3
*/

QUERY
SELECT
    ID,
    max(case when COLCOUNT = 1 THEN NAME end) AS COL1,
    max(case when COLCOUNT = 2 THEN NAME end) AS COL2,
    max(case when COLCOUNT = 3 THEN NAME end) AS COL3
FROM PIVOT
GROUP BY ID

/*
ID    COL1    COL2    COL3
1    COL101        COL102        COL103   
2    COL201        COL202        COL203   
*/

Setting Up a User Exit on DB2 UDB V8 to Archive Database Logs

Setting Up a User Exit on DB2 UDB V8 to Archive Database Logs

DB2 manually archiving transaction logs to TSM using user exit

have you missed any logs being archived to TSM user exit because of some problem in TSM server or User exit itself.

here is how you do it manually:-

db2uext2 -OS<os> -RL<release> -RQ<request> -DB<dbname> -NN<nodenumber>  -LP<logpath> -LN<logname> [-AP<adsmpasswd>]

to set all this option best way is to look at userexit  ARCHIVE.LOG  . you will have entry as below.

********************************************************************************
Time Started:      Fri Aug 22 18:01:55 2008

Parameter Count:      8
Parameters Passed:
Database name:     TST_PROD
Logfile name:      S0024899.LOG
Logfile path:      /transaction_logs/NODE0000/
Node number:       NODE0000
Operating system:  Linux/Z64
Release:           SQL08029
Request:           ARCHIVE
System Action:     ARCHIVE from /transaction_logs/NODE0000/ file S0024899.LOG to TSM for database NDB_PROD
Media Type:        TSM
User Exit RC:      0
Time Completed:    Fri Aug 22 18:01:56 2008

********************************************************************************

now you have all the option parameters required.

#db2uext2 -OSLinux/Z64 -RLSQL08029 -RQARCHIVE -DBTST_PROD -NNNODE0000  -LP/transaction_logs/NODE0000/ -LNS0039440.LOG

after you archive the logs you can cross verify using db2adutl command.

#db2adutl query logs db TST_prod

DB2 9 certification: Everything you need to know

DB2 9 certification: Everything you need to know

DB2 9 certification: Everything you need to know

Blog at WordPress.com.

Up ↑