DB2 Control Center Code Page
Submitted by Vikram Khatri on Tue, 2009-12-22 22:25.If you are using DB2 Control Center on Windows and wondering how to set the code page of your choice, follow these steps.
Open a DB2 command window:
C:\>SET DB2CODEPAGE=1208 C:\>db2cc
The first command sets the environment variable DB2CODEPAGE to 1208 (UTF-8) and second command launches Control Center.
How would you know if the Control Center picked up the chosen code page or not?
In Control Center click Tools>Tools Settings and click on Command Editor tab. Check Display SQLCA Data and close the Tools settings window.
Now, right click on Database in CC and select "Query". This will open up DB2 Command Editor GUI window. Type any command and you will see the SQLCA data and this holds the key.
SQLCA Information
sqlcaid : SQLCA sqlcabc: 136 sqlcode: 0 sqlerrml: 70
sqlerrmc: 1 1208 VIKRAM SAMPLE QDB2/NT 86 86 0 1208 1
sqlerrp : SQLRI01F
sqlerrd : [1]: -2147221503 [2]: 1 [3]: 0
[4]: 0 [5]: 0 [6]: 0
sqlwarn : [1]: [2]: [3]: [4]: [5]: [6]:
[7]: [8]: [9]: [10]: [11]:
The sqlerrmc shows the 1208 codepage of the database and second codepage after SAMPLE is also shown as 1208 which was picked up through the DB2CODEPAGE setting. You would have seen 1252 as code page if you had not used DB2CODEPAGE environment variable.
Why do we have to go through the above trouble? Just to see the data properly displayed in the GUI window.
Federation of Oracle Data in DB2
Submitted by Vikram Khatri on Mon, 2009-11-02 19:42.Steps to configure Oracle database federation from DB2 on zLinux. (Or any kind of *nix)
These steps are for Oracle database (any supported version from Oracle) to DB2 9.5 but they should work as it is for DB2 9.7 or later with proper Oracle client install.
1. Install DB2
3. Install Relational connectors
or Install InfoSphere DB2 (The InfoSphere DB2 is a limited edition of DB2 with relational connectors to connect to other relational databases such as Oracle, SQL Server, Sybase etc.). You do not need to install IBM branded DataDirect ODBC drivers in order to connect to Oracle since we will be installing Oracle client to connect to Oracle from DB2 server. (InfoSphere DB2 is used to be known as IBM Webspehere Federation Server.)
Install Oracle Client
Get the proper Oracle client to install it on your zLinux (or any kind of *Nix) DB2 server.
Follow these steps to make Oracle and DB2 talking to each other properly.
1. Find out the group id which was used to create a DB2 instance. You can do ls -l in your DB22 home directory to find out the group name. If you followed default group name, it could be db2iadm1. We will use this group name to create oracle user name under which we will install the Oracle client.
2. Create a user id for Oracle client such as oracle and specify the same group name to which DB22 instance belongs to. We are doing this so that we do not have to change the file permissions in Oracle client install for DB2 instance to access Oracle client's files.
3. Launch Oracle client installation GUI to install the Oracle client. Apply right patches as recommended by the Oracle.
4. After install is complete, ask your Oracle DBA to give you tnsnames.ora entries so that you can copy those entries in your tnsnames.ora file. Take help from your Oracle DBA to complete this step.
5. Login as Oracle user and type command tnsping to successfully ping the Oracle database from the zLinux.
6. Type $ echo $ORACLE_HOME and note down the entry. You will need this information when you configure federation for Oracle tables.
7. Login as DB2 instance user and add following entries to your .bashrc.
export ORACLE_HOME=/opt/oracle/product/10.2/db_1 export PATH=$ORACLE_HOME/bin:$PATH
Change the ORACLE_HOME to the same value that you got in step-6.
8. Logout and login as db2 instance user. Type command tnsping and you should be able to successfully ping.
9. Make a connection to your Oracle database using your SQL*Plus.
After you have make a successful connection to Oracle from your DB2 instance, you are now ready to install Oracle relational connector to create nick names for the Oracle tables in DB2.
Install InfoSphere DB2 or DB2 / Relational Connectors
Install DB2 either using IBM InfoSphere DB2 or install DB2 relational connectors in your existing DB2 instance where you like to do federation from other data sources like SQL Server, Oracle etc.
Follow the GUI install for InfoSphere DB2 or relational connectors for an existing DB2 instance and complete the installation. On the Select features to Install screen, select Oracle data source support only.
After GUI install is over, complete these steps to make sure that we selected right choices for the Oracle database. The GUI install also builds library libdb2net8F.so and this might fail if GUI can not find g++ command.
Go to your ~/sqllib/lib64 directory and you should see main library for Oracle libdb2net8.so. The other 2 additional files are libdb2net8F.so and libdb2net8U.so.
Go to the $HOME/sqllib/cfg directory. Check contents of the db2dj.ini file and you can make changes in this file if you were not sure of specified information during the GUI install.
$ cat db2dj.ini DJX_ODBC_LIBRARY_PATH=/opt/ibm/WSII/odbc/lib ODBCINI=/home/db2inst1/odbc.ini ORACLE_HOME=/opt/oracle/product/10.2/db_1
Make sure that the ORACLE_HOME matches with the ORACLE_HOME entry that you specified in your .bashrc file. The DJX_ODBC_LIBRARY_PATH and ODBCINI entries are for the SQL Server.
If you make changes to the db2dj.ini file, please stop and start the instance.
$ db2stop force $ db2start
Now, you are ready to create Oracle wrapper etc. The easiest way to create these without making mistakes is to use the DB2 Control Center GUI tool. You can save commands in a file for each step so that you can use the script in future when you need to configure it again on other servers. This is the best approach to do it right.
1. Make sure that you have DBM CFG param FEDERATED=YES.
$ db2 get dbm cfg | grep -i federate Federated Database System Support (FEDERATED) = YES
2. Launch db2cc either on your server or from your client machine. Catalog DB2 system, instance and database name.
A. Create a Wrapper. Specify ORACLE_HOME value in the variable window.
B. Create a Server Definition. Specify NODE name as the Oracle Service name or Oracle SID. This is important for a proper connection to establish.
C. Create User Mappings.
D. Create Nicknames. You should be able to discover the Oracle tables names. If that does not work, repeat the steps to make sure that your ORACLE_HOME, SID etc are defined correctly.
You have now nick names for the Oracle database tables that you selected.
Federation of SQL Server Data in DB2
Submitted by Vikram Khatri on Mon, 2009-11-02 15:00.Steps to configure SQL Server federation from zLinux. (Or any kind of *nix)
These steps are for DB2 9.5 but they should work as it is for DB2 9.7 or later with proper install of IBM branded ODBC drivers.
1. Install DB2
2. Install IBM Branded DataDirect ODBC Drivers.
3. Install Relational connectors
or Install InfoSphere DB2 (The InfoSphere DB2 is a limited edition with relational connectors to connect to other relational databases such as Oracle, SQL Server, Sybase etc.). But, you have to install IBM branded DataDirect ODBC drivers before installing InfoSphere DB2. (InfoSphere DB2 is used to be known as IBM Webspehere Federation Server.)
Install IBM branded DataDirect ODBC Drivers
For DB2 9.5, download IBM branded DataDirect ODBC drivers for zLinux from this link:
You can download IBM branded DataDirect ODBC drivers for other *nix for DB2 9.5 and higher from this link.
After unzipping contents of the DataDirect ODBC files, run ./odbcsetupbin to setup SQL Server ODBC drivers in your zLinux. Specify the directory where you are installing DataDirect ODBC drivers.
The GUI will install ODBC drivers at the location you specified. But, there are some manual steps that one must complete for a successful connection to SQL Server before installing DB2 relational connectors.
In your .bashrc of DB2 instance user, add following entries.
export ODBCINI=$HOME/odbc.ini export DJX_ODBC_LIBRARY_PATH=/opt/ibm/WSII/odbc/lib export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DJX_ODBC_LIBRARY_PATH export LANG=en_US
Adjust DJX_ODBC_LIBRARY_PATH to the path where you installed your ODBC drivers. Logout and login again for new entries to take effect or source your profile again as shown below.
$ source .bashrc
Create odbc.ini in the home directory of your DB2 instance user and add entries as per information for your SQL Server database.
[ODBC Data Sources] mssqlserver=Microsoft SQL Server [ODBC] Trace=0 TraceDll=/opt/ibm/WSII/odbc/lib/odbctrc.so InstallDir=/usr/opt/ibm/WSII/odbc quotedID=yes [sqlserverdb] Driver=/opt/ibm/WSII/odbc/lib/VMmsss23.so Description=Microsoft SQL Server Driver for S390 Address=xxx.xxx.xxx.xxx,portnumber Database=DBNAME QuotedId=No
We need to make sure that the shared library for SQL Servers are setup properly before we attempt to connect to SQL Server using DataDirect provided sample programs.
The SQL Server ODBC drivers shared library for DB2 9.5 are as follows:
DataDirect Version 4.2:
VMmsss22.so libVMicu22.so
DataDirect Version 4.3:
VMmsss23.so libVMicu23.so
Go to the home directory of the DB2 Instance user and type command ldd /opt/ibm/WSII/odbc/lib/VMmsss23.so. You might see the output similar to the one shown below:
$ ldd VMmsss23.so libpthread.so.0 => /lib64/libpthread.so.0 librt.so.1 => /lib64/librt.so.1 libVMicu23.so => /opt/ibm/WSII/odbc/lib/libVMicu23.so libodbcinst.so => /opt/ibm/WSII/odbc/lib/libodbcinst.so libnsl.so.1 => /lib64/libnsl.so.1 libdl.so.2 => /lib64/libdl.so.2 libc.so.6 => /lib64/libc.so.6 libstdc++.so.5 => /usr/lib64/libstdc++.so.5 libm.so.6 => /lib64/libm.so.6 libgcc_s.so.1 => /lib64/libgcc_s.so.1 /lib/ld64.so.1 (0x000002aaaa000)
You might notice that libVMicu23.so and libodbcinst.so are not resolved in your output of the ldd command. Fix these errors by creating proper symbolic links before you go to the next step. The ldd command should be able to resolve all dependencies before you attempt to go to the next step.
Change directory to /usr/lib and type these commands:
$ cd /usr/lib $ ln -s /opt/ibm/WSII/odbc/lib/libodbcinst.so libodbcinst.so $ ln -s /opt/ibm/WSII/odbc/lib/libVMicu23.so libVMicu23.so
Test connection to your SQL Server:
Change directory to /opt/ibm/WSII/odbc/example and run ./example program.
Specify the data source name that you specified in your $HOME/odbc.ini file . Specify user id and password to connect to SQL Server. If connection succeeds, you are good to go to the next step. If ./example is not able to make a connection to the SQL server, fix this problem first before you attempt to install relational connectors in DB2 to access SQL Server.
Install InfoSphere DB2 or DB2 and Relational Connectors
Please note: You should do this step only when you are able to test connection to your SQL Server using IBM branded DataDirect ODBC drivers. If previous step fails, you will have no luck connecting to SQL Server from DB2 federation.
Install DB2 either using IBM InfoSphere DB2 or install DB2 relational connectors in your existing DB2 instance where you like to do federation from other data sources like SQL Server, Oracle etc.
Follow the GUI install for InfoSphere DB2 or relational connectors on an existing DB2 instance and complete the installation. On the Select features to Install screen, select SQL Server data source support only. If you want to select Oracle as an additional data source, you will have to install Oracle client first before it can install support for Oracle. This is important.
Note: If your installation fails, check if you have g++ installed on your zLinux. You can check which g++ to see if g++ is available or not. If not, install g++ first on your zLinux. If you are using SUSE Linux, use YAST to install g++. On Redhat, you can use yum to install g++.
After GUI install in over, complete these steps to make sure that we are using right choices for the SQL Server. The GUI install also builds library libdb2mssql3F.so and this might fail if GUI can not find g++ command.
Go to your ~/sqllib/lib64 directory and you should see main library for SQL Server libdb2mssql3.so. The other 2 additional shared library files are libdb2mssql3F.so and libdb2mssql3U.so.
Go to the $HOME/sqllib/cfg directory. Check contents of the db2dj.ini file and you can make changes in this file if you were not sure of specified information during the GUI install.
$ cat db2dj.ini DJX_ODBC_LIBRARY_PATH=/opt/ibm/WSII/odbc/lib ODBCINI=/home/db2inst1/odbc.ini
Make sure that DJX_ODBC_LIBRARY_PATH and ODBCINI matches with the entries that you specified in your .bashrc file. You might wonder why there is a need to have entries at 2 different places. Please remember that DB22 process to connect to SQL Server will not read the environment variables set through your login profile file and db2dj.ini is the special file that will provide this information to the relational wrapper library.
If you make changes to the db2dj.ini file, please stop and start the instance.
$ db2stop force $ db2start
Now, you are ready to create SQL Server wrapper etc. The easiest way to create these without making mistakes is to use the DB2 Control Center GUI tool. You can save commands in a file for each step so that you can use the script in future when you need to configure it again on other servers. This is the best approach to do it right.
1. Make sure that you have DBM CFG param FEDERATED=YES.
$ db2 get dbm cfg | grep -i federate Federated Database System Support (FEDERATED) = YES
2. Launch db2cc either on your server or from your client machine. Catalog DB2 system, instance and database.
A. Right click on Federated Database Objects and click on Create Wrapper.
B. Select Data Source as SQL Server and it should automatically select library libdb2mssql3.so. Type name of the wrapper as per your choice and click on Show SQL and copy the command.
C. When you click on OK, it might ask you to specify LD_LIBRARY_PATH and SHLIB path. We have already specified our LD_LIBRARY_PATH in our .bashrc file so you can ignore this safely. Click Cancel.
D. Right click on Server Definition and click Create.
E. Specify any Name for your SQL Server and the Version of your SQL Server database.
F. Go to the Settings tab and specify DBNAME and NODE. The DBNAME is the name of your SQL Server database and NODE is the name of the data source entry that you created in your $HOME/odbc.ini file. The node name must match with the DSN in your odbc.ini file and DBNAME must match with the SQL Server database name. This is important. Click on Show SQL and save the command for the future use.
G. Right click on User Mappings and click Create. You will map your SQL Server user id and password here. Select your DB2 user id and click on the Settings tab. Specify remote user id and password for your SQL Server database. Click on Show SQL and save the command for the future use.
H. Right click on Create Nicknames and click on Create. Click on Discover. You should be able to see SQL Server tables for the schema that you selected. Click on Show SQL and save the commands for the future use.
You have now nick names for the SQL Server tables that you selected.
DB2 SQL PL - MESSAGE_TEXT
Submitted by Vikram Khatri on Sun, 2009-06-21 19:34.When you want SP to return message_text and you also want to handle error code as per your choice, follow this simple example.
CREATE PROCEDURE myproc(
OUT v_msg VARCHAR(300))
LANGUAGE SQL
BEGIN
DECLARE V_ERROR INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 V_MSG = MESSAGE_TEXT;
SET V_ERROR = SQLCODE;
END;
SET v_msg = '';
-- DO SOME SQL that throws the error and do conditional processing but want to see detailed
-- error message so that you can see what went wrong.
IF V_ERROR < 0 THEN
RETURN -6786; -- Code that want to process later on return through application
END IF;
END
The OUT parameter v_msg will give you detailed error message that DB2 will throw and you handled error as per your condition code.
New Exciting Features in DB2 9.7
Submitted by Vikram Khatri on Wed, 2009-06-03 17:28.This is by no means an exhaustive list of new features in DB2 9.7. But, it is the list of features that are most important to me as a DBA or a developer.
Online Schema Changes
See Burt's article on Online Schema changes.
CGTT - Created Global Temporary Table
Now, it is possible to create GLOBAL TEMPORARY TABLE so that they are in catalog instead of them only declarabale in a session. This has several benefits including - 1. CGTT under authorization controls, 2. Views, indexes and triggers can be defined on CGTT, 3. CGTT and DGTT can contain LOBS.
CREATE GLOBAL TEMPORARY TABLE DGTT_TABLE ( EMP_ID INTEGER, HIRE_DATE TIMESTAMP(3) )
Parameter Markers
Parameters in a procedure can have default values.
You can skip parameters with default values in a CALL statement.
Named parameters allows values to be assigned by name in any order.
SSL Connection in DB2
DB2 allows to use SSL connection between a java client and server. Server and client configurations are required for SSL connection to take place.
Server Configuration
- Use the iKeyman GUI tool (comes with GSKit) to create a certificate database (a.k.a KeyStore file)
- Import the server digital certificate you purchased from a certificate authority (CA) into the certificate database
- Set the following DBM configuration parameters
- SSL_SVR_KEYDB: Key store file
- SSL_SVR_STASH: Stash file
- SSL_SVCENAME: SSL port
- Optionally, select a ciphers suite
- SSL_CIPHERSPECS: Allowed ciphers suite
- SSL_VERSIONS: Allowed SSL/TLS versions
- Enable SSL communication for the instance
- db2set DB2COMM=SSL or db2set DB2COMM=SSL,TCPIP
Client Configuration
- Use the ikeyman GUI tool to create a signer certificate database
- Import the public key of the certificate authority into the certificate database
Change in connection properties
....
properties.put("sslConnection", "true");
System.setProperty("javax.net.ssl.trustStore", "/home/db2inst1/client.jks");
System.setProperty("javax.net.ssl.trustStorePassword", "myPassword");
....
con = java.sql.DriverManager.getConnection(url, properties);
How SSL Handshake works?
- Client requests an SSL connection listing its SSL version and supported cipher suites
- Server responds with a selected cipher suite
- Server sends its digital certificate to the client
- Client verifies the validity of the server's certificate (server authentication)
- Client and server securely negotiate a session key
- Client and server securely exchange information using the key selected above
XML Enhancements
Several new features for XML processing has been added. Few notables are:
- Full support of pureXML
- XML docs in XDA object can also be compressed
- Online reorg of XML indexes
- XML column can be used in UDFs (Not external UDF as you will still use XML as CLOB)
- Allow XML columns in a MDC table
- DECOMP from query CLP command : To shred XML documents. DECOMPOSE XML DOCUMENTS IN <select_statement>
XMLSCHEMA <xmlschemaname> - DECOMP from query Stored Procedure : sysproc.xdb_Decomp_XML_From_Query
- ADMIN_IS_INLINED and ADMIN_EST_INLINE_LENGTH functions can be used to know if XML or LOB is inlined and what is the length.
Examples of DECOMP:
DECOMPOSE XML DOCUMENTS IN 'SELECT DOCID, SALESDOC FROM ABC.SALESTAB' XMLSCHEMA ABC.SALES MESSAGES /home/myid/errors/errorreport.xml CALL XDB_DECOMP_XML_FROM_QUERY ( 'ABC', 'SALES', 'SELECT DOCID, SALESDOC FROM ABC.SALESTAB', 0, 0, 0, NULL, NULL, 1, :hnumInput, :hnumDecomposed, :herrorreportBuf);
Note: XDA = object where XML documents are stored that are not inlined in the base table
CLPPLUS vs SQL*Plus
DB2 has an equivalent of Oracle's SQL*Plus which is known as CLPPlus. If you know how to use SQL*Plus, you will find it almost same as SQL*Plus to use.
PL/SQL in DB2
This sounds the most promising new feature of DB2 where you can use PL/SQL of Oracle in DB2. But, remember Oracle has more than 20 years of development in PL/SQL so there will still be some missing functionality but it will come in the product as time passes and based upon demands from people.
The PL/SQL compiler in DB2 is native and not some form of masking layer that will translate PL/SQL to DB2 SQL PL. This allows PL/SQL code to be profiled and source level debugging can be done.
List of DBMS packages supported:
- DBMS_OUTPUT
- UTL_FILE
- DBMS_ALERT
- DBMS_PIPE
- DBMS_JOB
- DBMS_LOB
- DBMS_SQL
- DBMS_UTILITY
- UTL_MAIL
- UTL_SMTP
List of PL/SQL Features supported:
- All language logic supported like IF, WHILE := etc
- EXCEPTION - Try/catch handling
- User Defined Exceptions - Define conditions with or without SQLCODEs
- Constant variables - Variables that cannot be set
- FOR over range - Step through numbers
- FOR over SELECT - Step through result set of query
- FOR over cursor - Step through result set of cursor
- %TYPE - Anchored scalar data types
- %ROWTYPE - Anchored row types
- BULK COLLECT/FETCH - Aggregate result set into array
- FORALL - Pipe array into SQL statement
- AUTOMONOUS transaction - Executes a procedure in an independent TX
- Anonymous block
- Scalar function
- Procedure
- Package
- Trigger
- SYNONYM ON PACKAGE
List of Oracle Types in DB2
- NUMBER
- VARCHAR2 - NULL = '', trailing blank sensitive collation
- TIMESTAMP(n) - 0 (date + time) <= N <= 12 (date + time + picoseconds)
- DATE - Year to seconds, SYSDATE
- BOOLEAN - In procedural code
- INDEX BY - In procedural code
- VARRAY - In procedural code
- Row Type - In procedural code
- Ref Cursor - Allows passing, and predefining of cursors
Partial List of important Oracle functions in DB2
- TO_CHAR, TO_DATE, TO_TIMESTAMP, TO_NUMBER, TO_CLOB
- EXTRACT, ADD_MONTHS, ...
- INITCAP, RPAD, LPAD, INSTR, REVERSE, ...
- DECODE, NVL, LEAST, GREATEST, BITAND
List of Oracle SQL in DB2
- CONNECT BY
- (+)-join
- DUAL
- ROWNUM
- NEXTVAL/CURRVAL
- MINUS
- Unnamed inline views
- TRUNCATE table
- Public synonym
- CREATEd temp table
Separation of duties
Gone are the days when SYSADM used to be GOD in DB2. Not any more with separation of duties in place now.
- With the advent of SECADM (database level), implicit DBADM has been removed from SYSADM
- SECADM can be granted to ROLE and GROUPS now
- SECADM can now grant/revoke database and object auths
- DBADM can be setup in such a fashion so that it can not see data and do grant/revoke
- DBADM does not get secondary grants implicitly
- New Authorities are created such as EXPLAIN, DATAACCESS, ACCESSCTRL, SQLADM, WLMADM
Automatic Storage
Several new features are added to automatic storage in DB2. Few notable are:
- You can alter a database to add additional storage paths but they are used when existing storage paths become full. What if you want to strip data across all 4 storage paths immediately to gain parallelism. You use ALTER TABLESPACE myts REBALANCE to rebalance the data. You can use ALTER TABLESPACE myts REDUCE command to reduce the high water mark.
- You can DROP a storage path by using ALTER DATABASE DROP STORAGE PATH ON 'path1'. The storage path is changed to DROP PENDING and all table spaces are marked as An underlying storage Path(s) has been dropped. The DROP PENDING storage path is removed when all table spaces using it are rebalanced using ALTER TABLESPACE ts1 REBALANCE
- ALTER TABLESPACE ts1 REDUCE command will free trapped spaces which did not allow high water mark to come down in earlier DB2 versions.
- You can convert NON AUTOMATIC STORAGE database to AUTOMATIC STORAGE (AS) database.
- ALTER DATABASE ADD STORAGE is supported on non-AS databases
- Above command adds a storage pool for the database
- This allows creation of new table spaces with automatic storage
- You can convert existing non-AS DMS table space to an AS table space
- ALTER TABLESPACE … MANAGED BY AUTOMATIC STORAGE
- The new growth comes from new storage paths added to the database
- Old containers can be removed via DROP or REBLANCE.
- REDIRECTED RESTORE can be done from old DMS to new table space with automatic storage.
ONLINE TABLE MOVE
The online table move can be used in several circumstances as mentioned below:
- You want to do an ONLINE REORG or REDISTRIBUTE
- Want to do ONLINE TABLE compression
- Change pagesize ONLINE
- Online conversion to LARGE table space
- Move data/index/long data to new/different tablespaces
- Add or remove column, change column data type, enlarge columns
- Add / change MDC dimensions, range partitioning or partitioning key
The ONLINE TABLE move is accomplished by system stored procedure ADMIN_MOVE_TABLE(). This stored procedure allows to move data from an existing table to a new table object. When the data is moved, access to the source table is still available for SELECT, INSERT, UPDATE and DELETE operations.
This stored procedure has 4 phases.
- INIT: Select copy index, create triggers, create target and staging tables. Triggers are created to capture changes to the source table.
- COPY: Row by row copy (over index) of rows from source to target. LOAD may also be used optionally.
- REPLAY: Rows captured in the staging table are re-copied from the source table. It may require multiple iterations, depending on transaction volume.
- SWAP: Ready target table for swap – create indexes, statistics, optional REORG, etc. Source table is locked in share mode for final replay. Source table is renamed, target table renamed to original source name.
COMPACT MDC Tables
The MDC tables in DB2 are very unique and normally they do not require REORG if no DELETE are happening. The DELETE operation can make a MDC table sparse and how to get those unused pages back to the table space so that they can be used by other tables.
REORG TABLE mdctable RECLAIM EXTENTS ONLY
The above command is very fast as it is really not a reorg but marks empty blocks as unallocated in MDC table's block map and in table space SMPs.
Inline LOBS
Take advantage of compression by inlining LOBS. This is how this can be done.
CREATE TABLE … EMP_PHOTO BLOB(10MB) INLINE LENGTH 20000; ALTER TABLE … ALTER COLUMN EMP_PHOTO SET INLINE LENGTH 20000;
LOBs greater than inline length will be stored on LOBS table space but others will be stored inline alone with regular table data in the data page thus making it eligible for the table compression. There can be a mix of inline or non-inline LOBS in a table.
DB2 can still put LOBS inline even if no INLINE LENGTH is set. It will happen when a LOB's actual length is less than the size of its descriptor.
Local Range Partition Indexes
DB2 had global indexes on range partition tables. Now, you can have local indexes on range partition tables leading to faster Roll-in and Roll-out of the data.
C:\>clpplus db2admin/password@localhost:50001/SAMPLE Database Connection Information Hostname = localhost Database server = DB2/NT SQL09070 SQL authorization ID = db2admin Local database alias = SAMPLE Port = 50001 CLPPlus: Version 1.0 Copyright (c) 2009, IBM CORPORATION. All rights reserved. SQL> create tablespace tbsp1; DB250000I: The command completed successfully. SQL> create tablespace tbsp2; DB250000I: The command completed successfully. SQL> create tablespace tbsp3; DB250000I: The command completed successfully. SQL> create tablespace tbsp4; DB250000I: The command completed successfully. SQL> create tablespace tbsp5; DB250000I: The command completed successfully. SQL> create tablespace tbsp6; DB250000I: The command completed successfully. SQL> CREATE TABLE TAB1(c1 INT) 2 PARTITION BY RANGE (c1) 3 (STARTING FROM (1) ENDING (50) IN tbsp1 INDEX IN tbsp2, 4 ENDING(100) IN tbsp3 INDEX IN tbsp4, 5 ENDING(150) IN tbsp4 INDEX IN tbsp6); DB250000I: The command completed successfully. SQL> CREATE INDEX IDX1 ON TAB1(c1) PARTITIONED; DB250000I: The command completed successfully.
You can do partition level INDEX REORG, partition level TABLE REORG but RUNSTATS is still operates on TABLE level.
When you do an ATTACH and required indexes are already created, it is a very quick linked-in to the main table otherwise indexes will be created automatically. DETACH unlinks the partition from the main table quickly and it becomes just a regular table.
New Currently Committed (CC) Isolation
It is just a variation of CURSOR STABILITY (CS) isolation level of DB2 where writers do not block readers. It is just to please people who are very fond of Oracle's snapshot based concurrency model. Oracle implements the theory of writers not blocking readers with snapshot technology whereas DB2 uses regular LOGS to implement this.
The CC isolation level is the new DEFAULT isolation level instead of old CS isolation level. DB2 can do full lock avoidance with CURRENTLY COMMITTED isolation level thus requiring no application level change in the application if porting from Oracle to DB2. CC is based on data available on the logs. DB2 first goes to the log buffer to find the data. Since the updating transaction is still active, probably the data is still in the log buffers. Otherwise, it goes to the log files on disk. This type of lock avoidance does not need ROLLBACK SEGMENTS as required by Oracle since DB2 uses LOG BUFFER records or at most going back to LOGS on disks.
Statement Concentrator
Do you remember that Oracle used CURSOR_SHARING=YES to convert literals into parameter markers to avoid repeated compilation of SQL statements? Do you also remember lots of pain associated with this approach to have sub-optimal plan generated by Oracle optimizer? I remember people hated this feature in Oracle due to serious performance problems but DB2 has implemented this feature with some serious thought to avoid sub-optimal plan which can be avoided with the use of REOPT parameter for STATIC or DYNAMIC statements. Consider using DB2 Statement Concentrator with REOPT for applications that are notorious to use literals across the board.
TEMP TABLE Compression
The temp tables are compressed automatically if deep compression is licenced. There is no additional step required by the user to use the TEMP TABLE compression feature.
Index Compression
You can now compress indexes in DB2 and you do it by adding COMPRESS YES in CREATE INDEX or ALTER INDEX syntax.
Query to estimate compression index savings:
SELECT index_name, pages_saved_percent, compress_attr, index_compressed
FROM TABLE SYSPROC.ADMIN_GET_INDEX_COMPRESS_INFO
('T', 'myschema', 'T1', '', '')) AS T
How much space was saved by using index compression?
After compressing index, rerun above query to find out actual saving.
ALTER INDEX index1 COMPRESS YES REORG INDEXES ON TABLE t1 RUNSTATS ON TABLE t1
Unused indexes, tables and packages in DB2
Submitted by Vikram Khatri on Wed, 2009-05-27 22:05.Starting version DB2 9.7, you can use following query to detect unused indexes in DB2.
SELECT INDSCHEMA, INDNAME, TABNAME FROM SYSCAT.INDEXES WHERE LASTUSED = '01/01/0001';
The column LASTUSED tells when that index was last accessed in DB2. This is not per se a guaranteed solution that will show right value in LASTUSED column when you query it since DB2 will figure out when to update this column on its own. However, you can use db2pd tool to know definitely when an index was last used since database was activated.
This query will work only in DB2 V9.7 or higher but you can still use db2pd tool for other versions. The LASTUSED column has been added to SYSCAT.TABLES, SYSCAT.INDEXES and SYSCAT.PACKAGES. So, you will be able to figure out unused tables, indexes and packages starting DB2 9.7 using simple queries rather than interpreting output from the db2pd tool.
$ db2pd -d sample -tcbstats index
When you run db2pd tools for SAMPLE database, use tcbstats option and provide index argument to it. You will see a long output but you need to look for a column SCANS when you see TCB Index information. Again, you have to co-relate the Index ID (IID) to the index name through the catalog tables.
Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:09:45 TCB Table Information: Address TbspaceID TableID PartID MasterTbs MasterTab TableName 0x7C6EF8A0 0 1 n/a 0 1 SYSBOOT 0x7A0AC6A0 2 -1 n/a 2 -1 INTERNAL TCB Table Stats: Address TableName Scans UDI RTSUDI 0x7C6EF8A0 SYSBOOT 1 0 0 0x7A0AC6A0 INTERNAL 0 0 0 TCB Index Information: Address InxTbspace ObjectID TbspaceID TableID MasterTbs 0x7A0ABDA8 0 5 0 5 0 0x7A0ABDA8 0 5 0 5 0 TCB Index Stats: Address TableName IID EmpPgDel RootSplits BndrySplts PseuEmptPg Scans 0x7A0ABDA8 SYSTABLES 9 0 0 0 0 0 0x7A0ABDA8 SYSTABLES 8 0 0 0 0 0
The output above is trimmed for brevity. So, relate IID to the index name and look for the indexes having Scans=0.
If your database has been active for a month, you can run the db2pd tool and find out the indexes which have not been scanned in last one month.
All of this information is only transient and available from the time your database was activated when you use db2pd tool. However, the LASTUSED column in SYSCAT.TABLES, SYSCAT.INDEXES and SYSCAT.PACKAGES will persist the information and it is meant to give you an idea when that object was last accessed. Please remember that DB2 on z/OS provided this capability for a long time and DB2 LUW is now providing same capability.
Online Schema Changes - The Easy Way
Submitted by Burt Vialpando on Thu, 2009-05-21 01:32.I am going to show you an easy way to do online schema changes in DB2. The best to learn things is by doing some simple examples. My intent is to make you an expert in 1/2 hour if you learn the following concepts.
- Create a database with and without automatic storage enabled
- Add automatic storage to a database that does not have it enabled
- Convert a DMS table space to use automatic storage that was not previously using it
- Use DB2 administration views to check for automatic storage status
- Set up your database for automatic revalidation of objects
- Use create with error (create objects that reference other objects that do not yet exist and marking them invalid)
- Revalidate invalid objects at access time
- Alter a table to change data types, change column names and to drop a column
- Show how a single alter table commands forces a table reorg if you wish to write to it
- Show how 3 alter table commands force a table reorg to be able to do more alter table commands
- Show how to use administration views to see invalid objects
Prerequisite
- Install DB2 9.7 on Windows if you do not already have it.
- Download scripts from here
- Unzip files in C:\
New automatic storage features
Adding automatic storage to a database
1. Open a Windows Command prompt
2. Change your directory to: C:\COBRA_LAB_SCRIPTS\SCHEMA
3. To open a DB2 CLP window, type: db2cmd
4. In the Windows Explorer, review command script SCHEMA01.CMD (Right click-> Edit. Do not double click!)
5. Notice this script calls SCHEMA02.DDL. Review this script now. Notice this creates a database called SCHEMADB without automatic storage. It also creates a DMS table space that is of course not using automatic storage either.
6. Execute script SCHEM01.CMD.
7. The script takes about 90 seconds to create the database. Wait for the script to finish before trying to run any more commands from the CLP. While this is running, review the SCHEMA03.SQL script to see how the administrative view works that we will be running when this finishes. SCHEMA03.SQL checks to see if any table spaces are using automatic storage in our database.
8. When the first scrip finishes, execute the SCHEMA03.SQL script this way:
db2 -td; -vf SCHEMA03.SQL
9. When it is finished, notice the state of all the table spaces in our database:
10. Another way to verify that automatic storage is not enabled in this database is to try to create a table space using automatic storage. To do this, type the following in the DB2 CLP window:
db2 create tablespace new_ts
Note: We can run the above command because the previous script connected to the schemadb database for us. Since we have stayed in our CLP window we are still connected. If you accidentally close your CLP window, you might need to connect to the database again. To do this, use command “db2 connect to schemadb”.
11. The SQL20317N error message states that automatic storage has not been defined for this database. This is what some DB2 customers encounter in real life situations when the DBA either forgot to enable automatic storage or thought they did not need it and then turned out later they did need it after all. Prior to Cobra, there was nothing you could do about this except to backup the database and do a redirected restore to a newly created database with automatic storage enabled. Painful.
12. Another way you can check to see if your database is using automatic storage by running this command that uses a DB2 administration view.
db2 select db_storage_path from sysibmadm.snapstorage_paths
13. 0 records are selected because there are no storage paths for your database. A database storage path is the path (or paths) defined when you enable automatic storage.
14. DB2 now makes it easy to enable automatic storage. Let’s do that now. From the DB2 CLP window, enable automatic storage by typing the following:
db2 alter database add storage on ‘C:’
15. Make sure you use single quotes as shown.
16. You should get a message “SQL Command Completed Successfully”. You have now enabled automatic storage for this database with one easy command!
17. To check it, issue this command again. (hint: use up arrow key twice.)
db2 select db_storage_path from sysibmadm.snapstorage_paths
18. Your query returns one storage path, which is C:
19. Now, try to create a table space using automatic storage again. (hint: use up arrow key)
db2 create tablespace new_ts
20. This command is successful because we can now use the automatic storage feature in our database. Verify this table space creation by running the script that lists the table spaces and their automatic storage usage. (Again, use up arrow key.)
db2 -td; -vf SCHEMA03.SQL
So, we verified that NEW_TS was created using automatic storage.
Adding automatic storage to a table space
21. Now we want to see if there are any DMS table spaces that we can convert to automatic storage that are not using it. Run this SQL, which uses a DB2 administration view to see this information. (Hint: use the up arrow key to bring your last command back and change the script name.)
db2 -td; -vf SCHEMA04.SQL
22. Your output shows table space called SYSTOOLSPACE is DMS and does not use automatic storage
23. Let’s list the containers for these two table spaces. Use this command which will call SQL that uses another DB2 administration view.
db2 -td; -vf SCHEMA05.SQL
24. Notice that NEW_TS is using the automatic storage path and SYSTOOLSPACE is using a default DMS path.
25. We can convert table space SYSTOOLSPACE to use automatic storage with the following command:
db2 alter tablespace systoolspace managed by automatic storage
26. You have now added the automatic storage path to this table space. To see this run this command again:
db2 -td; -vf SCHEMA05.SQL
27. Notice that SYSTOOLSPACE is using both its old container and the new automatic storage container.
28. DB2 will keep this scenario and use both containers unless you force it to do otherwise. Use this command to force a rebalance:
db2 alter tablespace systoolspace rebalance
29. When the rebalance is done, check your containers again by using this script one last time:
db2 -td; -vf SCHEMA05.SQL
30. Notice now that SYSTOOLSPACE only uses the automatic container.
31. You can also check to see if SYSTOOLSPACE is using automatic storage by running this script again:
db2 -td; -vf SCHEMA04.SQL
32. Notice SYSTOOLSPACE is now using automatic storage
Automatic Object Revalidation
1. At the heart of the online schema changes capability, is a new database configuration parameter called AUTO_REVAL. In order to let DB2 defer the checking of the validation of objects, set this parameter with this command:
db2 update db cfg using auto_reval deferred_force
2. Execute this command to check if this is set (note: this is case sensitive):
db2 get db cfg | FindStr AUTO_REVAL
3. Use the Windows Explorer to review script SCHEMA10.CMD. (Right click-> Edit.) See that it calls SCHEMA11.DDL. Review SCHEMA11.DDL carefully.
4. First, notice DB2 will perform the CREATE OR REPLACE functionality. This is an important online schema change ability. It means that we can avoid using DROP in order to recreate a view which preserves grants to the view.
5. Second, notice also that the view references a function not yet created, and then the function references a table not yet created. Won’t this DDL fail if it is attempted to be run in this order? Normally, yes, but with AUTO_REVAL set as we did earlier, it will work.
6. Run SCHEMA10.CMD by typing its name:
SCHEMA10.CMD
7. Review the output file SCHEMA10_OUTPUT.TXT. Notice that the view is created, but marked invalid. The function is also created, but marked invalid. This is referred to as create with error.
8. Keep reviewing this output file. Notice that the view and function are both auto revalidated when referenced later with the SELECT. Notice that the SELECT is from the view, which uses the function, which references the table. Everything revalidates and works just fine!
NOTE: The table, function and view created here can be deleted using script SCHEMA49.DDL. But only use this if you are playing with this feature after the lab is complete. Don’t use it now.
Altering a table and keeping it online
Multiple table alters in a script
1. Next, we will alter our table SCHEMA_CHANGE_TB to see how DB2 handles doing table alters. Review both command scripts: SCHEMA12.CMD which executes SCHEMA13.DDL. (Use right click -> Edit)
2. Notice that the column called salary will change name to salary_amount.
3. Notice that the column called dept will change from an SMALLINT to a CHAR(10)
4. Notice that column ssn is dropped.
5. Also notice the script has a REORG at the end of it. We’ll explain why later.
6. Run this script to see how the alter works:
SCHEMA12.CMD
7. Review file SCHEMA12_OUTPUT.TXT. Notice the alter commands succeed and the table stays read accessible.
Alter table #1
8. So, how did this alter work? Let’s look more closely at altering column data types to understand what DB2 is doing. Use this command to do our first alter to our table:
db2 alter table schema_change_tb alter column dob set data type timestamp
9. The command is successful. What does this mean for our table? Let’s try to select from it:
db2 select * from schema_change_tb fetch first 1 rows only
10. The command is successful. What does this mean for our table? Let’s try to select from it:
11. Notice the DATE column is now TIMESTAMP. The ALTER worked just fine and so did the SELECT after it was altered.
12. Try an update to the table. Use this command:
db2 update schema_change_tb set job_code = ‘WORKR’ where job_code=’WKR’
13. Oops. We are not allowed to do a write to the table. UPDATES and INSERTS will not work while this table is in this state. We will have to do a REORG of the table to make it write accessible. We’ll do this later in the lab.
14. Recap: We can read the table after the alter data type, but we cannot write to it.
Alter table #2
15. Now, let’s experiment with altering this table some more (hint: use your up arrow key and make changes to your previous script…)
db2 alter table schema_change_tb alter column job_code set data type integer
16. Notice, DB2 does not allow this. You have character data in the job_code column and DB2 knows it cannot be changed to integer data. If your job_code column had only ‘1’, ‘2’, ‘3’, ‘555’, etc. then this command could be done because DB2 could cast that data to an integer.
17. Now try this command (hint: use your up arrow key again…)
db2 alter table schema_change_tb alter column job_code set data type char(1)
18. DB2 does not allow this either and gives you the same error message. It knows that even though the source and target columns are both CHAR, there is data in the table that would be truncated if it reduced the column size, so it will not let you do this operation.
19. Try this command:
db2 alter table schema_change_tb alter column job_code set data type char(10)
20. This worked. This is the second successful alter command to this table in this series. Column JOB_CODE was originally data type CHAR(4), so moving this data into a larger character column CHAR(10) is OK.
Alter table #3
21. Try this command:
db2 alter table schema_change_tb alter column job_code set data type varchar(20)
22. This succeeded too.
23. DB2 can move data within compatible data types and the target data type is larger than the source, so the data fits nicely and the command is successful.
24. This is the third successful alter command in this series.
25. Try to do one more alter table command:
26. DB2 gives us a new error message: an invalid state error code. What this means is that DB2 only allows you three successful sessions of altering the same table before it requires a REORG on that table.
27. To be able to perform writes to an altered table or to be able to perform more alters on this table, use this command:
db2 reorg table schema_change_tb
28. When DB2 returns that “the reorg command completed successfully”, then you can do more alters to that table.
29. Now try this same command again (hint: use up arrow)
db2 alter table schema_change_tb alter column job_code set data type char(5)
30. The command is successful now for two reasons:
- We performed a reorg on the table, which restarts the 3 alter limit
- We are allowed to truncate the table to a smaller column size because DB2 knows that the largest data value in that column is 4 bytes (what it was originally.) Even though that column was expanded to CHAR(10) and later to VARCHAR(20), the new room we gave this column was not used. Had we inserted data of more than 5 characters after growing the column size, then we would not have room to shrink this column to CHAR(5) later.
31. Now make sure you reorg this table so you can write to it:
db2 reorg table schema_change_tb
32. Now, try writing to the table (use up arrow key to bring this command back)
db2 update schema_change_tb set job_code = ‘WORKR’ where job_code=’WKR’
33. The command succeeds because we did a reorg on the table after the alter.
Revalidating invalidated objects
34. So, how does all of this altering of our table affect other related objects? If you want to check if this has invalidated anything, use this command:
db2 select objectname from syscat.invalidobjects
35. Notice the view that references this table has been made invalid. If you want to try to revalidate this view, simply access it:
db2 select * from schema_change_supervisors_vw
36. We have a problem. The column SALARY is referenced in our view, but we changed it earlier to SALARY_AMOUNT. Auto revalidation did not work in this case because the view definition is not able to work with the altered table as-is.
37. We will have to fix our view. Review and run script
db2 -td; -vf SCHEMA14.DDL
Our view has been fixed and it runs fine.
38. Now check the invalid objects catalog view again:
db2 select objectname from syscat.invalidobjects
A word about SOFT invalidation: Soft invalidation allows for you to drop an object, even when running transactions are using it. The Cobra default for soft invalidation is ON. If you wish to turn this off and require an exclusive lock on an object in order to drop it, set the following registry variable like this:
db2set DB2_DDL_SOFT_INVAL=OFF
Java Program Name for DB2
Submitted by Vikram Khatri on Thu, 2009-04-30 22:45.Finally DB2 implemented a long standing demand for setting the Java application name so that you could see the name through DB2 utilities instead of seeing db2jcc_applica.
You set a connection property clientProgramName to your chosen name and the db2 utilities will pick up this name instead of the default name of db2jcc_application.
java.util.Properties conProp = new java.util.Properties();
String url = "jdbc:db2://localhost:50000/GSDB";
Class.forName("com.ibm.db2.jcc.DB2Driver");
conProp.put("user", "myUserName");
conProp.put("password", "myPassword");
conProp.put("clientProgramName", "MyProgram");
connection = DriverManager.getConnection(url, conProp);
When you list the application, you will see the name that you set.
C:\Documents and Settings\Administrator>db2 list applications Auth Id Application Appl. Application Id DB # -------- -------------- ---------- ------------------------------ -------- ---- DBAPOT db2jcc_applica 993 127.0.0.1.48648.090430221610 GSDB 1 DBAPOT MyProgram 997 127.0.0.1.50184.090430221627 GSDB 1 DBAPOT db2jcc_applica 996 127.0.0.1.49416.090430221613 GSDB 1 DBAPOT db2jcc_applica 995 127.0.0.1.49160.090430221612 GSDB 1 VIKRAM db2bp.exe 988 *LOCAL.DB2.090430221558 GSDB 1 DBAPOT db2jcc_applica 994 127.0.0.1.48904.090430221611 GSDB 1
DB2 LUW Automatic Memory Management Configuration Tips on LINUX Servers
Submitted by Mark Mulligan on Mon, 2009-04-27 09:24.There are DB2 LUW configuration parameters that can be set to AUTOMATIC and managed by the DB2 automatic memory manager (STMM). One advantage of this is that DB2 will automatically adjust the memory it uses to get the work done based on the characteristics of the current work load and it will continue to adjust itself dynamically as processing continues. Below is a list of configuration parameters for DB2 V9.5 that can be set to AUTOMATIC.
Here is a list of database manager (instance) configuration values for a partitioned environment that can be set to AUTOMATIC. Note that the SHEAPTHRES can be set to 0 and this combined with other database level configuration parameters can result in DB2 sort memory being managed by the automatic memory manager.
db2 attach to db2inst1
db2 get dbm cfg show detail
Database monitor heap size (4KB) (MON_HEAP_SZ) = AUTOMATIC(4096)
Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC(889708)
Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(0)
Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(2000)
Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)
Number of pooled fenced processes (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)
No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(4096)
No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC(2048)
Sort heap threshold (4KB) (SHEAPTHRES) = 0
Here is a list of database configuration values that can be set to AUTOMATIC. Note that the CATALOGCACHE_SZ may be set to -1 and that this results in it’s memory size being derived by the formula (MAXAPPLS*4). If MAXAPPLS is set to AUTOMATIC then the databases catalog cache memory size is indirectly managed by DB2’s automatic memory manager.
db2 connect to db2db1
db2 get db cfg for db2db1
Size of database shared memory (4KB) (DATABASE_MEMORY) = CALCULATED
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(12097)
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(98)
Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(3465)
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(152076)
Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(3041)
Database heap (4KB) (DBHEAP) = AUTOMATIC(20000)
SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(4096)
Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(2000)
Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(6928)
Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC(6600)
Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC(1)
Number of I/O servers (NUM_IOSERVERS) = AUTOMATIC(18)
Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC
Max number of active applications (MAXAPPLS) = AUTOMATIC(430)
Average number of active applications (AVG_APPLS) = AUTOMATIC(1)
Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4)
Change self tuning memory to off and STMM is off. If you do not change the configuration values then they are set to the last value STMM used.
Self tuning memory (SELF_TUNING_MEM) = ON (Active)
LINUX Configuration Tip: If you want to use the DB2 LUW Automatic Memory Manager feature on a LINUX server then the maximum segment size (SHMMAX) should be configured to the same size as the maximum total shared memory (SHMALL) and the maximum total shared memory (SHMALL) should be configured to the total physical memory on the server.
DB2 Configuration Tip: If the maximum segment size is less than the maximum total shared memory for LINUX then you should avoid using AUTOMATIC on configuration parameters that manage the amount of shared memory that DB2 will use or you will experience problems if DB2 exceeds these limits. The same is true for the maximum total shared memory if it is set to something smaller than the maximum physical memory on the server. The SHMMAX and SHMALL LINUX configuration values limit the amount of shared memory DB2 can use on a LINUX server. If DB2 is configured to exceed these limits or if the DB2 automatic memory manager adjusts DB2 shared memory so that it exceeds these limits then LINUX will stop the DB2 threads when these limits are exceeded.
Here is the formula to use when configuring DB2 to run within the max segment memory size of an operating system like LINUX.
(util_heap_sz * 4096) +
(dbheap * 4096) +
(catalogcache_sz * 4096) +
(locklist * 4096) +
(pckcachesz * 4096) +
(bufferpool_1 * bufferpool_page_size) +
(bufferpool_n * bufferpool_page_size)
<= LINUX maximum segment size (SHMMAX)
You can determine the current values of the DB2 database utility heap size, database heap, catalog cache, lock list and package cache by connecting to the database and running the db2 get database configuration command with the show detail option.
db2 connect to db2db1
db2 get db cfg for db2db1 show detail
You can determine the current values for the DB2 bufferpools in a database by running the SQL select statement listed below. If you see a -2 value then the bufferpool is configured to have it’s memory managed by DB2’s automatic memory manager. Note, that size in bytes of memory for a bufferpool may be calculated by multiplying page size times the number of pages (PAGESIZE * NPAGES).
db2 -v "select substr(bpname,1,15) as bpname,pagesize,npages from syscat.bufferpools order by bpname"
select substr(bpname,1,15) as bpname,pagesize,npages from syscat.bufferpools order by bpname
BPNAME PAGESIZE NPAGES
--------------- ----------- -----------
BPTBL32K 32768 40960
BPTMP04K 4096 1000
BPTMP08K 8192 1000
BPTMP16K 16384 1000
BPTMP32K 32768 4395
IBMDEFAULTBP 16384 19000
In order to determine how a LINUX server is configured run the command ipcs –l.
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 262144 SHMMAX
max total shared memory (kbytes) = 8388608 SHMALL
min seg size (bytes) = 1
------ Semaphore Limits --------
max number of arrays = 1024
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 32
semaphore max value = 32767
------ Messages: Limits --------
max queues system wide = 1024
max size of message (bytes) = 65535
default max size of queue (bytes) = 65536
If you multiply the SHMMAX size times 1024 (Kbytes) then you will have the number of bytes available for a segment on the LINUX server. If this matches the SHMALL value and if the SHMALL value matches the total physical memory on the server then you are free to use AUTOMATIC for DB2 LUW database configuration values for utility heap size, database heap, catalog cache, lock list, package cache and bufferpools. If SHMMAX is less than SHMALL or if SHMALL is less than the physical memory available on the LINUX server then you should configure utility heap size, database heap, catalog cache, lock list, package cache and bufferpools so that the total bytes of shared memory they use is equal to or less than the LINUX segment maximum limit of shared memory limit, which ever is smaller.
You can set up a spreadsheet with formulas that will help you make sure that your DB2 LUW database configuration and bufferpool configuration will be equal to or less than the max seg size limit on the LINUX server. Here is an example of one way you could do this.
Build an INSERT statement
Submitted by Vikram Khatri on Wed, 2009-04-22 12:21.Use this DB2 Stored Procedure to build an INSERT statement. This is very useful when you are developing some Java or .Net program or any other program and need an easy way to do this.
Thanks to Serge Rielau of DB2 Toronto lab for sharing this with the DB2 community. You can also download the script from here.
CREATE TABLE RESULT(text CLOB(2M));
DROP PROCEDURE insertscript;
--#SET TERMINATOR @
CREATE PROCEDURE insertscript(IN ptabschema VARCHAR(128),
IN ptabname VARCHAR(128),
IN pbatchsize INTEGER,
IN pterminator VARCHAR(2))
BEGIN
DECLARE SQLCODE INTEGER;
DECLARE SQLSTATE CHAR(5);
DECLARE inserttext CLOB(2M);
DECLARE selecttext CLOB(2M);
DECLARE insertheader CLOB(2M);
DECLARE colname VARCHAR(128);
DECLARE colcount INTEGER;
DECLARE colno INTEGER;
DECLARE rowcount INTEGER;
DECLARE insertcount INTEGER;
DECLARE colvalue CLOB(128);
DECLARE typeid SMALLINT;
DECLARE done SMALLINT;
DECLARE selectstmt STATEMENT;
DECLARE selectcur CURSOR WITH HOLD FOR selectstmt;
IF NOT EXISTS(SELECT 1 FROM SYSCAT.TABLES
WHERE tabschema = ptabschema
AND tabname = ptabname) THEN
SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'Table not found (remember case!)';
END IF;
DELETE FROM result;
SET insertheader = 'INSERT INTO "' || ptabschema || '"."'
|| ptabname || '" VALUES ';
SET selecttext = 'SELECT colno, colvalue, typeid FROM "'
|| ptabschema || '"."' || ptabname || '" AS T, LATERAL(VALUES ';
FOR column AS SELECT colno, colname, typename, codepage FROM SYSCAT.COLUMNS
WHERE tabschema = ptabschema
AND tabname = ptabname
ORDER BY colno
DO
SET typeid = CASE WHEN typename IN ('SMALLINT', 'INTEGER', 'BIGINT',
'FLOAT', 'DOUBLE', 'REAL', 'DECFLOAT',
'DECIMAL') THEN 1
WHEN typename IN ('CHAR', 'VARCHAR', 'GRAPHIC', 'VARGRAPHIC',
'CHARACTER', 'CLOB', 'DBCLOB') THEN
CASE WHEN codepage = 0 THEN 3 ELSE 2 END
WHEN typename = 'BLOB' THEN 4
ELSE RAISE_ERROR('78000', 'Unsupported type') END;
SET selecttext = selecttext
|| CASE WHEN colno > 0 THEN ',' ELSE '' END
|| '(SMALLINT(' || CHAR(colno)
|| '), ' || CASE WHEN typeid = 1
THEN 'TRIM(CHAR(T."' || colname || '")), '
WHEN typeid = 3
THEN 'HEX(T."' || colname || '"), '
WHEN typeid = 4
THEN 'HEX(VARCHAR(T."' || colname || '")), '
ELSE 'VARCHAR(T."' || colname || '"), ' END
|| CHAR(typeid) || ')';
SET colcount = colno;
END FOR;
SET selecttext = selecttext || ') AS S(colno, colvalue, typeid)';
PREPARE selectstmt FROM selecttext;
SET rowcount = 0;
SET insertcount = 0;
SET done = 0;
OPEN selectcur;
mainloop: WHILE rowcount < pbatchsize DO
IF rowcount = 0 THEN
SET inserttext = insertheader;
END IF;
subloop: LOOP
FETCH selectcur INTO colno, colvalue, typeid;
IF SQLCODE = 100 THEN
SET done = 1;
LEAVE subloop;
END IF;
SET inserttext = inserttext
|| CASE WHEN colno = 0 AND rowcount = 0 THEN '('
WHEN colno = 0 THEN '), ' || CHR(10) || chr(13) || '('
ELSE ',' END
|| CASE WHEN colvalue IS NULL THEN 'NULL'
WHEN typeid = 1 THEN colvalue
WHEN typeid = 2 THEN '''' || colvalue || ''''
WHEN typeid = 4 THEN 'BLOB(x''' || colvalue || ''')'
ELSE 'x''' || colvalue || '''' END;
IF colno = colcount THEN
SET rowcount = rowcount + 1;
END IF;
IF rowcount >= pbatchsize THEN LEAVE subloop; END IF;
END LOOP;
SET inserttext = inserttext || ')' || pterminator;
IF rowcount > 0 THEN
INSERT INTO RESULT VALUES inserttext;
SET rowcount = 0;
SET insertcount = insertcount + 1;
IF MOD(insertcount, 100) = 0 THEN
COMMIT;
END IF;
ELSE
LEAVE mainloop;
END IF;
END WHILE;
END
@
--#SET TERMINATOR ;
CALL insertscript('myschema', 'mytab', 10, ';');
DB2 Update Statement using sub-query - Use MERGE
Submitted by Vikram Khatri on Thu, 2009-03-05 16:47.Thanks to Serge Rielau for insight in DB2 MERGE.
For example, if you have this Oracle UPDATE statement and wondering how can you use this in DB2. Here is the solution.
Oracle UPDATE
UPDATE
(
SELECT clm.c2 wc1,
m.c2 wc2
FROM schema1.table1 m,
schema1.table2 clm
WHERE clm.c1 = m.c1
and clm.date1 between to_date('10012008','MMDDYYYY') and to_date('12312008','MMDDYYYY')
and m.date2 between to_date('10012008','MMDDYYYY') and to_date('12312008','MMDDYYYY')
and m.flag = 'N'
) joined
SET joined.wc1 = joined.wc2;
DB2 Merge
MERGE INTO schema1.table1 m
USING schema1.table2 clm
ON clm.c1 = m.c1
and clm.date1 between to_date('10012008','MMDDYYYY') and to_date('12312008','MMDDYYYY')
and m.date2 between to_date('10012008','MMDDYYYY') and to_date('12312008','MMDDYYYY')
and m.flag = 'N'
WHEN MATCHED THEN UPDATE SET m.c2 = clm.c2;
SQL Server Update - 2 table issue
update t set book = r.book from #RecLookup r JOIN TABLE_TOBE_UPDATED t ON t.history_date = CONVERT ( char(8) , @CobDate, 112) and t.rec_id = r.rec_id where (r.update_options & 0x1) = 0x1 AND ((@MinRecId = 0 and @MaxRecId = 0x7FFFFFFF) OR (t.rec_id >= CONVERT(varchar(12),@MinRecId) and t.rec_id <= CONVERT(varchar(12),@MaxRecId)))
DB2 using MERGE
MERGE INTO TABLE_TOBE_UPDATED T
USING SESSION.TT_RECLOOKUP R
ON T.HISTORY_DATE = V_COBDATE
AND T.REC_ID = R.REC_ID
AND (BITAND(R.UPDATE_OPTIONS,1)) = 1
AND ((V_MINRECID = 0 AND V_MAXRECID = 2147483647) OR
(T.REC_ID >= V_MINRECID AND T.REC_ID <= V_MAXRECID))
WHEN MATCHED THEN UPDATE SET
T.BOOK = R.BOOK;
Or, another MERGE. The explain plan will tell you which one is better.
MERGE INTO
(
SELECT REC_ID, BOOK FROM TABLE_TOBE_UPDATED
WHERE HISTORY_DATE = V_COBDATE
AND ((V_MINRECID = 0 AND V_MAXRECID = 2147483647) OR
(REC_ID >= V_MINRECID AND REC_ID <= V_MAXRECID))
) AS T
USING
(
SELECT REC_ID, BOOK FROM SESSION.TT_RECLOOKUP
WHERE (BITAND(UPDATE_OPTIONS,1)) = 1
) AS R
ON T.REC_ID = R.REC_ID
WHEN MATCHED THEN UPDATE SET
T.BOOK = R.BOOK;
SQL Server UPDATE returning more than one row for each matched row
Take this example of notorious UPDATE in SQL Server where more than one row is returned for each row matched in the table_tobe_updated. This is a feature of SQL Server you realize when porting to DB2. When you try to match this in DB2, you might get -811 error.
The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row.. SQLCODE=-811, SQLSTATE=21000
SQL Server Update returning more than one row for each matched row
update tmp
set
trading_book_id = bk_Riskmap.book_id,
trading_book_name = bk_Riskmap.book_name,
party_book_name = bk_Riskmap.counterparty_book_name,
party_book_id = bk_Riskmap.counterparty_book_id,
cost_center = bk_Riskmap.cost_center,
Risk_Reason = bk_Riskmap.risk_reason,
mapping_type = (tmp.mapping_type | 0x2)
from TABLE_TOBE_UPDATED tmp
JOIN (
select book_name, edsi_id, book_hist_id = MIN(book_hist_id)
from dbo.book_hist
where history_date = @CobDate
and (options & 0x2) = 0x2
group by book_name, edsi_id
) UList
ON tmp.book = UList.book_name
and tmp.edsi_id = UList.edsi_id
JOIN dbo.book_hist bk_Riskmap
ON bk_Riskmap.history_date = @CobDate
and UList.book_hist_id = bk_Riskmap.book_hist_id
DB2 UPDATE - USING MERGE
MERGE INTO TEMP.TABLE_TOBE_UPDATED TMP
USING
(
SELECT ULIST.BOOK_NAME, ULIST.EDSI_ID, MIN(ULIST.BOOK_HIST_ID) BOOK_HIST_ID
FROM BOOK_HIST UList
WHERE UList.HISTORY_DATE = '2009-03-31'
AND BITAND(UList.OPTIONS,2) = 2
GROUP BY ULIST.BOOK_NAME, ULIST.EDSI_ID
) AS U (BOOK_NAME, EDSI_ID, BOOK_HIST_ID)
ON TMP.BOOK = U.BOOK_NAME
AND TMP.EDSI_ID = U.EDSI_ID
WHEN MATCHED THEN UPDATE SET
(TRADING_BOOK_ID,TRADING_BOOK_NAME,PARTY_BOOK_NAME,
PARTY_BOOK_ID,COST_CENTER,RISK_REASON,MAPPING_TYPE) =
(
SELECT BOOK_ID, BOOK_NAME,
COUNTERPARTY_BOOK_NAME,
COUNTERPARTY_BOOK_ID,
COST_CENTER,
RISK_REASON,
BITOR(TMP.MAPPING_TYPE, 2)
FROM BOOK_HIST BK_RISKMAP
WHERE BK_RISKMAP.HISTORY_DATE = '2009-03-31'
AND U.BOOK_HIST_ID = BK_RISKMAP.BOOK_HIST_ID
)@
Rule of thumb: You should never have to use TARGET table in the USING clause.
Run DB2 Scripts from Java Programs
Submitted by Vikram Khatri on Thu, 2009-01-15 03:03.If you have DB2 scripts that you want to run through your Java programs, you normally use Runtime.getRuntime().exec method to run system commands. The following sample code tells how to run DB2 scripts.
The following code shows it for both Windows and Unix systems. The trick here is to run db2cmd with /c /i /w switches to make it working properly. For Unix systems, it assumes that you are running this program from db2 enabled shell or you have sourced your db2profile. Same way, you use ksh shell with -c switch and pass all arguments through a array variable.
Through this program, you are passing full path name of the db2 script. We also change directory to the parent of the script so that all other dependent scripts residing in the directory do not fail. This is not so much about Java programming but how to use shell capability to run multiple commands.
import java.io.BufferedReader;
import java.io.File;
import java.io.InputStreamReader;
import java.text.SimpleDateFormat;
import java.util.Date;
public class RunDB2Script implements Runnable
{
private static final SimpleDateFormat timestampFormat =
new SimpleDateFormat("yyyy-MM-dd HH.mm.ss.SSS");
private static String osType = (System.getProperty("os.name").toUpperCase().
startsWith("WIN")) ? "WIN" : (System.getProperty("os.name").toUpperCase()
.startsWith("Z/OS")) ? "z/OS" : "OTHER";
private String db2ScriptName = "";
public RunDB2Script(String db2ScriptName)
{
this.db2ScriptName = db2ScriptName;
}
public void run()
{
String line = null;
Process p = null;
BufferedReader stdInput = null, stdError = null;
try
{
File f = new File(db2ScriptName);
String dirName = f.getParent();
if (osType.equalsIgnoreCase("win"))
{
p = Runtime.getRuntime().exec("db2cmd /c /i /w " + "cd " + dirName +
" && db2 -tvf " + db2ScriptName);
stdInput = new BufferedReader(new InputStreamReader(p.getInputStream()));
stdError = new BufferedReader(new InputStreamReader(p.getErrorStream()));
while ((line = stdInput.readLine()) != null)
{
if (!(line.equals("")))
log(line);
}
while ((line = stdError.readLine()) != null)
{
if (!(line.equals("")))
log(line);
}
} else
{
String cmd[] = {"/bin/ksh","-c", "cd " + dirName +
" ; db2 -tvf " + db2ScriptName};
p = Runtime.getRuntime().exec(cmd);
stdInput = new BufferedReader(new InputStreamReader(p.getInputStream()));
stdError = new BufferedReader(new InputStreamReader(p.getErrorStream()));
while ((line = stdInput.readLine()) != null)
{
log(line);
}
while ((line = stdError.readLine()) != null)
{
if (!(line.equals("")))
log(line);
}
}
stdInput.close();
stdError.close();
p.getInputStream().close();
p.getErrorStream().close();
} catch (Exception e)
{
e.printStackTrace();
}
}
private static void log(String msg)
{
if (osType.equals("z/OS"))
{
System.out.println(timestampFormat.format(new Date()) + ":" + msg);
} else
{
System.out.println("[" + timestampFormat.format(new Date()) + "] " + msg);
}
}
}
Matt Devlin's Perl Scripts for DB2
Submitted by Vikram Khatri on Sat, 2009-01-10 14:51.Are you looking for Matt Devlin's Perl Scripts for DB2? Even though, they are old but still they are relevant with new release of DB2 particularly if you know how to modify them. Matt Devlin's website http://mattdevlin.net is no longer available but you can get download his scripts here.
DB2 LOAD using ASC option through MTK
Submitted by Vikram Khatri on Thu, 2008-12-18 13:38.If you use MTK (Migration Toolkit from IBM) to migrate your database to DB2 on Windows platform, the migration goes smooth but you may see problem when you FTP data file and LOAD script to Unix platform if you do not do it right.
For example, you chose ASC (ASCII) option since you do not have an option of specifying a right delimiter for DEL option since your data contains CR, LF, TAB, Tilde(~), vertical bar(|) etc.
A sample LOAD statement generated by MTK using ASC option might look like following:
load from datafile.out of ASC MODIFIED BY reclen=2066 codepage=1208 nochecklengths striptblanks METHOD L (1 12,14 523,525 536,538 549,551 750,752 1261,1263 1462,1464 1469,1471 1492,1494 1505,1507 1507,1509 1534,1536 2045) NULL INDICATORS (0,2048,0,2050,2052,2054, 2056,2058,2060,2062,0,2064,2066) messages messages.msg replace INTO SCHEMA.TABLENAME nonrecoverable
You will notice a record length of 2066 and null indicators are specified at fixed position.
When you FTP data, you need to make sure that you use BINARY option since you do not want embedded CRLF to be replaced by LF using ASCII transfer option.
This simple trick will save you tons of time figuring out why LOAD does not work on Unix platform.
DB2 Extract Database Size Information from UNIX List Commands
Submitted by Mark Mulligan on Wed, 2008-11-26 18:43.Mark Mulligan – (mark.mulligan.sr@sbcglobal.net) DB2 DBA
29 November 2008
Introduction
Have you ever run the db2 "call get_dbsize_info(?,?,?,0)" command to get the size of your DB2 LUW database and noticed that the size is much smaller than the space used on your UNIX file systems? Do you need to monitor database growth over time to help plan for future storage requirements?
This article describes a script that will calculate the size of a DB2 LUW database on a UNIX server using UNIX commands that will give you a more accurate measure of the space used by the database. This script also stores this information in an operational table that you may use as part of your DBA Monitoring Dash Board to help you monitor and manage space in relation to growth trends.
Pros and cons
The advantage of monitoring database sizes over time is that you can see trends and show evidence to make the case for future storage acquisitions. This information can also be helpful in showing you when significant spikes in data growth appear and you can confirm that these are expected and not due to operational problems with application jobs or programs.
A disadvantage to this approach is that it does not include the size of transaction log files on disk, archive log files if you keep them on disk or temporary tablespaces. If you wish to include that information in the database size you will have to enhance the script to do this.
Description
The DB2 list tablespace command and list tablespace container command can be used to obtain the path or file for tablespace containers in a database. The UNIX list command (ls) can be used to obtain the size of the tablespace container paths and files and accumulated by partition and summarized by database. This script captures partition and database size information and stores it in a table. If the script is run daily then the size information can be seen over time.
Having this partition and database size information available in a table named OPERATIONS.DATABASE_INFORMATION can show database size information for one day or over time.
In the two examples shown below, the call to the get_dbsize_info stored procedure returns a database size of roughly 1.36 terabytes and the db2extract_database_size.ksh script returns a database size of roughly 3.09 terabytes. The difference between these two approaches is significant and when it comes to terabytes of storage it is important to be accurate in your measurements for storage planning purposes. Also, if you notice significant size differences in the database partition sizes then this could indicate an out of balance condition that you may need to correct at the tablespace or table level in the database.
db2 "call get_dbsize_info(?,?,?,0)" Value of output parameters -------------------------- Parameter Name : SNAPSHOTTIMESTAMP Parameter Value : 2008-11-26-09.11.44.205752 Parameter Name : DATABASESIZE Parameter Value : 1367017938944 Parameter Name : DATABASECAPACITY Parameter Value : -1 Return Status = 0 db2extract_database_size_information.ksh prodin01 proddb01 Server Instance Database Datetime Size Partition prodsvr prodin01 PRODDB01 20081126091344 2186406912 0 prodsvr prodin01 PRODDB01 20081126091344 784969437184 1 prodsvr prodin01 PRODDB01 20081126091344 781603337216 2 prodsvr prodin01 PRODDB01 20081126091344 780613206016 3 prodsvr prodin01 PRODDB01 20081126091344 750529480704 4 prodsvr prodin01 PRODDB01 20081126091344 3099901868032
Environment
The script in this article was tested on a server running the AIX operating system and DB2 versions 9.1 and 9.5. The KORN shell script should work on any server that has a UNIX-like operating system that supports these types of scripts. (Linux and Linux/390 come to mind.) This script is designed to run in a native UNIX environment and is not intended to run under the DB2 Script Center Client Window. The extract script must be run under the DB2 instance id or an id that has the correct UNIX permissions to be able to run the UNIX list command (ls) against the database tablespace container paths and files.
Before you get started, a couple of words of caution:
- Practice running the script and verify the results.
- Create the table before running the script. The DDL for this is provided below.
- Create tablespaces for the table and it’s index.
- Make sure the id you use to run the script has privileges to run the UNIX list (ls) command against the tablespace container paths and files and to insert rows in the OPERATIONS.DATABASE_INFORMATION table. Running this under a DB2 instance id would be best.
- Schedule this script to run in cron or some other scheduling system daily under a DB2 instance id and you will have a history of the database size information for you to use over time.
- WARNING: If you keep store other files on file systems in paths where DB2 keeps tablespace container files, these could be picked up and counted as part of your database size.
- WARNING: If IBM changes the output format of the DB2 list tablespaces show detail or DB2 list tablespace containers for id commands then this script may have to be modified to work with the new format.
CRONTAB schedule example
00 22 * * * /db2util/scripts/db2extract_database_size_information.ksh prodin02 proddb02 >/dev/null 2>&1
The script
#!/bin/ksh
#----------------------------------------------------------------------t
# Program : db2extract_database_size_information.ksh
# Description : This script runs the DB2 list tablespace command
# : to identify the tablespace id's in a partition
# : and then runs the db2 list tablespace containers
# : for each id that is not a temporary tablespace
# : and extracts the name for each container path
# : or file and then runs a UNIX list (ls) command
# : and accumulates the size of the files to
# : calculate the size of the partition and
# : ultimately the size of the database.
# Author : Mark Mulligan
# Date : 04/07/2005
# Input Arguments : 1st Parameter = (Required) Instance.
# : 2nd Parameter = (Required) Database.
# Output : None.
# Required Files : None.
# Maintenance :
#-----------------------------------------------------------------------
ScriptVer='04/07/2005'
ScriptName='db2extract_database_size_information'
Server=$(hostname)
if [ "$1" = "" ]
then echo ""
echo "Parm 1 Instance (required)"
echo "Parm 2 Database (required)"
echo 'Parm 3 Script Output Directory Path (optional) Defaults to /dbawork/reports'
echo ""
return
else Instance=`echo $1 | tr '[A-Z]' '[a-z]'`
fi
if [ "$2" = "" ]
then echo ""
echo "Parm 2 Database (required)"
echo 'Parm 3 Script Output Directory Path (optional) Defaults to /dbawork/reports'
echo ""
return
else Database=`echo $2 | tr '[a-z]' '[A-Z]'`
fi
if [ "$3" = "" ]
then OutputDir='/dbawork/reports'
else OutputDir=$3
fi
#.${HOME)/sqllib/db2profile
. ${HOME}/.${Server}_${Instance}
Datetime="`date +%Y%m%d%H%M%S`"
FileName=${OutputDir}/${ScriptName}_${Server}_${Instance}_${Database}_${Datetime}
OutputFile=${FileName}.txt
NumberOfNodes=`cat $INSTHOME/sqllib/db2nodes.cfg | wc -l`
TotalFileSize=0
PartitionNodeSize=0
LastNode=0
echo "Server Instance Database Datetime Size Partition" | tee $OutputFile
cat $INSTHOME/sqllib/db2nodes.cfg | while read node1 server node2
do
export DB2NODE=$node1
db2 terminate >/dev/null 2>/dev/null
db2 connect to $Database >/dev/null 2>/dev/null
db2 list tablespaces | grep "Tablespace ID" | while read tablespace ID equals id
do
db2 -x "select tbspaceid,datatype from syscat.tablespaces where tbspaceid=$id and datatype!='T'" >/dev/null 2>&1
if [ "$?" = "0" ]
then
db2 list tablespace containers for $id | grep Name | while read name equals path
do
echo ${path} >> $OutputFile.detail
ls -l ${path} | while read permissions number id group size month day hourminute filename
do
if [ "$permissions" = "total" ]
then :
else
# if [ "$path" = "$filename" ]
# then echo "$filename $size" >> $OutputFile.detail
# else echo "$path $filename $size" >> $OutputFile.detail
# fi
if [ "$node1" = "$LastNode" ]
then :
else
echo "$Server $Instance $Database $Datetime $PartitionNodeSize $LastNode" | tee -a $OutputFile
db2 -v "insert into operations.database_information values \
('${Server}','${Instance}','${Database}','${LastNode}', \
${PartitionNodeSize},current timestamp)" \
>/dev/null 2>&1
LastNode=$node1
PartitionNodeSize=0
fi
((PartitionNodeSize=PartitionNodeSize+size))
((TotalFileSize=TotalFileSize+size))
fi
done
done
fi
done
done
echo "$Server $Instance $Database $Datetime $PartitionNodeSize $LastNode" | tee -a $OutputFile
db2 -v "insert into operations.database_information values \
('${Server}','${Instance}','${Database}','${LastNode}',${PartitionNodeSize},current timestamp)" \
>/dev/null 2>&1
echo "$Server $Instance $Database $Datetime $TotalFileSize" | tee -a $OutputFile
db2 -v "insert into operations.database_information values ('${Server}','${Instance}','${Database}',' \
',${TotalFileSize},current timestamp)" 2>/dev/null 2>&1
echo "Output located in" | tee -a $OutputFile
ls $OutputFile* | tee -a $OutputFile
The Table
------------------------------------------------
-- DDL Statements for table "OPERATIONS"."DATABASE_INFORMATION"
------------------------------------------------
CREATE TABLE "OPERATIONS"."DATABASE_INFORMATION" (
"SERVER" CHAR(8) NOT NULL WITH DEFAULT ,
"INSTANCE" CHAR(8) NOT NULL WITH DEFAULT ,
"DATABASE" CHAR(8) NOT NULL WITH DEFAULT ,
"DBPARTITIONNUM" CHAR(3) ,
"SIZE" BIGINT NOT NULL WITH DEFAULT ,
"SIZE_TIMESTAMP" TIMESTAMP NOT NULL WITH DEFAULT )
DISTRIBUTE BY HASH("SIZE_TIMESTAMP")
IN "TSOPERATIONS01" INDEX IN IXOPERATIONS01 NOT LOGGED INITIALLY ;
Example
If you run the script in foreground without passing it any parameters, it will echo display the parameters that the script uses. The first two parameters for the script are required so that the common reusable DBA script will know what DB2 instance and database to use. The third parameter is optional.
db2extract_database_size_information.ksh Parm 1 Instance (required) Parm 2 Database (required) Parm 3 Script Output Directory Path (optional) Defaults to /dbawork/reports
When you run the script in foreground and pass it a DB2 instance id and database, you should see output displayed that is similar to the example shown below.
db2extract_database_size_information.ksh prodin01 proddb01 Server Instance Database Datetime Size Partition prodsvr prodin01 PRODDB01 20081126091344 2186406912 0 prodsvr prodin01 PRODDB01 20081126091344 784969437184 1 prodsvr prodin01 PRODDB01 20081126091344 781603337216 2 prodsvr prodin01 PRODDB01 20081126091344 780613206016 3 prodsvr prodin01 PRODDB01 20081126091344 750529480704 4 prodsvr prodin01 PRODDB01 20081126091344 3099901868032 Output located in /dbawork/reports/db2extract_database_size_information_prodsvr_prodin02_PRODDB02_20081126125715.txt /dbawork/reports/db2extract_database_size_information_prodsvr_prodin02_PRODDB02_20081126125715.txt.detail
Select Examples
The output created by this script is stored in a DB2 table. You may develop SQL or a script that runs different types of select SQL statements that will help you use this information to help you monitor database size changes over time. The first select statement shows you the size of all partitions in the database and the total database size yesterday. The second select statement shows you the database sizes only over time. This information could be presented in a graph to show the database size growth over time.
select database,dbpartitionnum,size,date(size_timestamp) date from operations.database_information where date(size_timestamp) = current date - 1 day order by database,size_timestamp DATABASE DBPARTITIONNUM SIZE DATE -------- -------------- -------------------- ---------- PRODDB02 0 2186406912 11/25/2008 PRODDB02 1 784969437184 11/25/2008 PRODDB02 2 781603337216 11/25/2008 PRODDB02 3 780613206016 11/25/2008 PRODDB02 4 750529480704 11/25/2008 PRODDB02 3099901868032 11/25/2008 select database,dbpartitionnum,size,date(size_timestamp) date from operations.database_information where dbpartitionnum = ' ' order by database,size_timestamp DATABASE DBPARTITIONNUM SIZE DATE -------- -------------- -------------------- ---------- PRODDB02 3095516920832 10/26/2008 PRODDB02 3095545461760 10/27/2008 PRODDB02 3095662148608 10/28/2008 PRODDB02 3095763762176 10/29/2008 PRODDB02 3095895849984 10/30/2008 PRODDB02 3096048811008 10/31/2008 PRODDB02 3096162221056 11/01/2008 PRODDB02 3096359111680 11/03/2008 PRODDB02 3096465312768 11/03/2008 PRODDB02 3096645143552 11/04/2008 PRODDB02 3096794696704 11/05/2008 PRODDB02 3096930421760 11/06/2008 PRODDB02 3097016273920 11/07/2008 PRODDB02 3097127816192 11/08/2008 PRODDB02 3097199893504 11/09/2008 PRODDB02 3097231940608 11/10/2008 PRODDB02 3097399696384 11/11/2008 PRODDB02 3097587784704 11/12/2008 PRODDB02 3097786883072 11/13/2008 PRODDB02 3097978125312 11/14/2008 PRODDB02 3098132265984 11/15/2008 PRODDB02 3098199473152 11/16/2008 PRODDB02 3098299218944 11/17/2008 PRODDB02 3098505722880 11/18/2008 PRODDB02 3098718649344 11/19/2008 PRODDB02 3098936228864 11/20/2008 PRODDB02 3099159116800 11/21/2008 PRODDB02 3099320859648 11/22/2008 PRODDB02 3099425651712 11/23/2008 PRODDB02 3099529197568 11/24/2008 PRODDB02 3099901868032 11/25/2008 PRODDB02 3099901868032 11/26/2008
Conclusion
If you do not have sophisticated database monitoring software that captures information about DB2 database sizes, you can capture this yourself from the DB2 database and UNIX operating system by using this script. This valuable source of information can be used by a DBA as evidence to show database size changes over time and used to help justify new storage requests. If you develop a dash board for application teams, users and management that gives them visibility into this information then they can monitor database size history over time and gain a better understanding about the growth of information stored and used by the company.
If you have any questions related to this article, feel free to contact me, Mark Mulligan, at mark.mulligan.sr@sbcglobal.net and I will be glad to try to answer your questions.
About the author
Mark Mulligan is a DBA living in the Dallas-Fort Worth area of Texas. He has a Bachelor of Science Degree in Business Administration from Merrimack College in North Andover, Massachusetts, and 30 years of experience working with computer software design, development, maintenance, enhancement and support. He has 10 years of experience working with DB2 on servers running UNIX-like operating systems and 15 years of experience working with DB2 on Z/OS. Mark can be contacted at mark.mulligan.sr@sbcglobal.net.
DB2 Load and Index Build Information from DB2 Diagnostic Log File - STMM Information Tool
Submitted by Mark Mulligan on Fri, 2008-11-21 11:44.Mark Mulligan – (mark.mulligan.sr@sbcglobal.net) DB2 DBA
16 November 2008
Introduction
If you have application jobs or users that run the DB2 load utility and would like to keep track of this information over time there is valuable information stored in the DB2 diagnostic log file that can help you accomplish this. Detail information about each table load and index build is recorded in the DB2 diagnostic log file.
This article describes the table load and index build information available and a script that will extract this information and insert it into a table that will help you monitor table load and index build performance over time. This script also extracts DB2 memory management message information (STMM) and imports that into another table.
Pros and cons
The advantage of having table load and index build information over time is that it can help you monitor and manage performance and answer questions about the performance of loads run by applications and users. If you use the DB2 automatic memory manager (STMM) then you will have information showing how this behaves over time as well.
One disadvantage of this approach is that the script relies on the current availability and format of information from the DB2 diagnostic log file and if IBM changes the format or content of this file in a future release, as has been done in the past, then this script will have to be modified or may become completely obsolete if the information is no longer available.
Description
The DB2 diagnostic log file named db2diag.log for a DB2 instance contains messages with information about table loads and index builds that are run against a database. Listed below are examples of the start and completion of a table load and the start and completion of the index build phase of the load. The script I developed will capture the server, instance, database, authid, table load or index build type, starting timestamp, completed timestamp and table name involved with the load. It will also capture information about what the DB2 automatic memory manager is doing during the day if you use that feature.
Having this table load and index build information available in a table named OPERATIONS.DB2DIAG_LOAD_INFORMATION, will allow you to see all of the table loads and index builds that run during a day. You will also be able to compare the performance of loads into tables from one day to the next. Having automatic memory management information available in a table named OPERATIONS.DB2DIAG_STMM_INFORMATION will give you insight into what the automatic memory manager is doing in your environments as well.
One example of how I use the table load and index build information is to help me identify scheduling problems that result in poor performance for other users or jobs. If a load job is running, other applications might wait to select information from the table and eventually time out and roll back. Being able to tell application teams and users that some other application or user was running a load at the time can help them understand the importance of proper scheduling and coordination of work activities.
When a load runs longer than normal and about the same number of rows are involved with the load, this information may show that the index build phase of the load ran longer. If loads are usually run with the incremental index build option, there are times when the database will override this and do a complete index rebuild and the longer index build times may indicate this.
The great thing about having this information available in a table is that you can provide physical evidence about what happened when and not just have to ask other people to take your word for it. If you make this table information available in an online dash board for application support people, users and management, then they can see for themselves what is going on and trend graphs can be developed using this information to show how loads are performing over time.
2008-11-20-00.19.46.435158-300 I13347A492 LEVEL: Warning PID : 4976964 TID : 1 PROC : db2lfrm0 0 INSTANCE: edwp02 NODE : 000 APPHDL : 0-718 APPID: 10.145.33.23.39186.081120051946 AUTHID : PP0342DL FUNCTION: DB2 UDB, database utilities, sqlulPrintPhaseMsg, probe:311 DATA #1 : String, 131 bytes LOADID: 6336514.2008-11-20-00.19.46.264479.0 (35;4) Starting LOAD phase at 11/20/2008 00:19:46.427305. Table MDDB .TRNCTN_ALRT 2008-11-20-00.19.46.522220-300 I14733A465 LEVEL: Warning PID : 106784 TID : 1 PROC : db2lrid 0 INSTANCE: edwp02 NODE : 000 APPHDL : 0-718 APPID: 10.145.33.23.39186.081120051946 AUTHID : PP0342DL FUNCTION: DB2 UDB, database utilities, sqlulPrintPhaseMsg, probe:311 DATA #1 : String, 105 bytes LOADID: 6336514.2008-11-20-00.19.46.264479.0 (35;4) Completed LOAD phase at 11/20/2008 00:19:46.517440. 2008-11-20-00.19.46.528250-300 I15199A465 LEVEL: Warning PID : 106784 TID : 1 PROC : db2lrid 0 INSTANCE: edwp02 NODE : 000 APPHDL : 0-718 APPID: 10.145.33.23.39186.081120051946 AUTHID : PP0342DL FUNCTION: DB2 UDB, database utilities, sqlulPrintPhaseMsg, probe:311 DATA #1 : String, 105 bytes LOADID: 6336514.2008-11-20-00.19.46.264479.0 (35;4) Starting BUILD phase at 11/20/2008 00:19:46.522519. 2008-11-20-00.19.46.943711-300 I15665A466 LEVEL: Warning PID : 106784 TID : 1 PROC : db2lrid 0 INSTANCE: edwp02 NODE : 000 APPHDL : 0-718 APPID: 10.145.33.23.39186.081120051946 AUTHID : PP0342DL FUNCTION: DB2 UDB, database utilities, sqlulPrintPhaseMsg, probe:311 DATA #1 : String, 106 bytes LOADID: 6336514.2008-11-20-00.19.46.264479.0 (35;4) Completed BUILD phase at 11/20/2008 00:19:46.940636.
Environment
The script in this article was tested on a server running the AIX operating system and DB2 versions 9.1 and 9.5. The KORN shell script should work on any server that has a UNIX-like operating system that supports these types of scripts. (Linux and Linux/390 come to mind.) This script is designed to run in a native UNIX environment and is not intended to run under the DB2 Script Center Client Window. This script may be run under any id that has access to a DB2 environment via the db2profile and that has the authority to run the db2 get dbm cfg command. This command is used to determine the default location of the db2diag.log file. The script requires that a DB2 instance id and database name be passed to it and has other parameters you may use to have it run against old copies of db2diag.log files if you move these off to another path daily like I do.
Before you get started, a couple of words of caution:
Practice running the script and verify the results.
Create the table before running the script. The DDL for this is provided below.
Create tablespaces for the table and it’s index.
Make sure the id you use to run the script has privileges to run the db2 get dbm cfg command and to be able to connect to the database and to insert and update rows in the OPERATIONS.DB2DIAG_LOAD_INFORMATION table and to import rows into the OPERATIONS.DB2DIAG_STMM_INFORMATION table.
Schedule this script to run in cron or some other scheduling system daily under a DB2 instance id and you will have a history of the table load and index build information for you to use over time.
WARNING: If you keep your db2diag.log file and never move it to another path and append a date timestamp to it then this script will run longer and longer every day and try to insert and update rows it processed the day before. I have a script that moves our db2diag.log files to a /dbawork/instance/db2dump path daily that renames the file to db2diag_CCYYMMDD*.log where CCYYMMDD* is a date timestamp. This script is designed to allow you to pass this path as the third parameter. If you just pass the path then it will process all db2diag*.log files. If you pass it the db2diag_CCYYMMDD*.log at the end of the path as shown in the crontab example below then the script will only process yesterday’s db2diag.log file.
NOTE: If you run the script without the third parameter, then the script will run db2 get dbm cfg to determine the location of the current db2diag.log file and then process that file.
WARNING: If IBM changes the format of the DB2 diagnostic log messages this script may need to be re-written or may become completely obsolete if the information is no longer available in the db2diag.log file.
CRONTAB schedule example
00 01 * * * /db2util/scripts/db2diag_extract_information.ksh edwp02 edwp02
/dbawork/edwp02/db2dump db2diag_CCYYMMDD*.log >/dev/null 2>&1
The script
#!/bin/ksh
#-----------------------------------------------------------------------
# Program : db2diag_extract_information.ksh
# Description : This script extracts information from db2diag
# : log files.
# Author : Mark Mulligan
# Date : 06/10/2008
#-----------------------------------------------------------------------
ScriptVer='06/10/2008'
ScriptName='db2diag_extract_information'
Server=$(hostname)
if [ "$1" = "" ]
then echo ""
echo "Parm 1 Instance (required)"
echo "Parm 2 Database (required)"
echo "Parm 3 DB2 Diagnostic Log Path (optional) Defaults to DIAGPATH"
echo ' May be a path where you put your db2diag_CCYYMMDDHHMMSS.log files'
echo ' For example, "/dbawork/edwp02/db2dump"'
echo "Parm 4 DB2 Diagnostic Log File (optional) Defaults to db2diag*.log"
echo ' May be db2diag_CCYYMMDD*.log'
echo "Parm 5 Script Output Directory Path (optional) Defaults to /dbawork/reports"
echo ""
return 8
else Instance=`echo $1 | tr '[A-Z]' '[a-z]'`
fi
if [ "$2" = "" ]
then echo ""
echo "Parm 2 Database (required)"
echo "Parm 3 DB2 Diagnostic Log Path (optional) Defaults to DIAGPATH"
echo ' May be a path where you put your db2diag_CCYYMMDDHHMMSS.log files'
echo ' For example, "/dbawork/edwp02/db2dump"'
echo "Parm 4 DB2 Diagnostic Log File (optional) Defaults to db2diag*.log"
echo ' May be db2diag_CCYYMMDD*.log'
echo "Parm 5 Script Output Directory Path (optional) Defaults to /dbawork/reports"
echo ""
return 8
else Database=`echo $2 | tr '[a-z]' '[A-Z]'`
fi
if [ "$3" = "" ]
then db2 get dbm cfg | grep DIAGPATH | read diagnostic data directory path diagpath equals DiagPath
Path="${DiagPath}"
else Path=${3}
fi
if [ "$4" = "" ]
then File="db2diag*.log"
else if [ "$4" = "db2diag_CCYYMMDD*.log" ]
then CCYYMMDD="`date +%Y%m%d`"
File="db2diag_${CCYYMMDD}*.log"
else File=${4}
fi
fi
if [ "$5" = "" ]
then OutputDir="/dbawork/reports"
else OutputDir="${5}"
fi
. $HOME/sqllib/db2profile
Datetime="`date +%Y%m%d%H%M%S`"
FileName=${OutputDir}/${ScriptName}_${Server}_${Instance}_${Database}_${Datetime}
OutputFile=${FileName}.txt
echo "$ScriptName Vers: $ScriptVer started on " `uname -n` on `date` | tee $OutputFile
echo "" | tee $OutputFile
echo "ls ${Path}/${File}" | tee -a $OutputFile
ls ${Path}/${File} > $FileName.ls
echo "" | tee $OutputFile
cat $FileName.ls | tee -a $OutputFile
cat $FileName.ls | while read filename
do
echo "Processing db2diag information in $filename" | tee -a $OutputFile
cat $filename | while read a b c d e f g h i j k l m n o p q r s t u v w x y z
do
# 2008-06-19-06.27.59.170148-240 I1503054A494 LEVEL: Warning
if [ "$c" = "LEVEL:" ]
then echo ${a} | sed 's/-/ /g' | read ccyy mo dd time extra
db2diagTimestamp="${ccyy}-${mo}-${dd}-${time}"
# echo $db2diagTimestamp
fi
# PID : 6807552 TID : 1 PROC : db2lfrm0 0
if [ "$a" = "PID" ]
then PID=$c
fi
# INSTANCE: edwp02 NODE : 000
if [ "$a" = "INSTANCE:" ]
then INSTANCE=$b
NODE=$e
fi
# APPHDL : 0-1738 APPID: 10.145.33.23.46241.080619102739
if [ "$a" = "APPHDL" ]
then APPHDL=$c
APPID=$e
fi
# AUTHID : EDWP02
if [ "$a" = "AUTHID" ]
then AUTHID=$c
fi
# LOADID: 3264648.2008-08-12-04.22.39.825964.0 (5;15)
if [ "$a" = "LOADID:" ]
then LOADID=$b
fi
# Starting LOAD phase at 06/19/2008 06:27:58.562570. Table MDDB .PIF_RPRT_DY_SM
if [[ "$a" = "Starting" && "$b" = "LOAD" ]]
then Type=$b
StartingLOADTimestamp=$db2diagTimestamp
TableName=$h$i
echo $TableName | sed 's/\./ /g' | read TabSchema TabName
# echo "$TabSchema"
# echo "$TabName"
db2 -x "insert into operations.db2diag_load_information values \
('${LOADID}','${Type}','${Server}','${Instance}','${Database}',${NODE},\
'${AUTHID}',${PID},'${APPHDL}','${APPID}',\
timestamp('${StartingLOADTimestamp}'), \
timestamp('${StartingLOADTimestamp}'), \
'${TableName}','${TabSchema}','${TabName}')" > /dev/null 2>&1
fi
if [[ "$a" = "Completed" && "$b" = "LOAD" ]]
then Type=$b
CompletedLOADTimestamp=$db2diagTimestamp
db2 -x "update operations.db2diag_load_information set completed_timestamp = \
timestamp('${CompletedLOADTimestamp}') where loadid = '${LOADID}' \
and type = '${Type}' and node = ${NODE}" > /dev/null 2>&1
fi
# Starting BUILD phase at 06/19/2008 06:28:02.441206.
if [[ "$a" = "Starting" && "$b" = "BUILD" ]]
then Type=$b
StartingBUILDTimestamp=$db2diagTimestamp
db2 -x "select tablename,tabschema,tabname from operations.db2diag_load_information \
where loadid = '${LOADID}' and type = 'LOAD' and node = ${NODE}" | \
read TableName TabSchema TabName
db2 -x "insert into operations.db2diag_load_information values \
('${LOADID}','${Type}','${Server}','${Instance}','${Database}',\
${NODE},'${AUTHID}',${PID},'${APPHDL}','${APPID}',\
timestamp('${StartingBUILDTimestamp}'),\
timestamp('${StartingBUILDTimestamp}'),\
'${TableName}','${TabSchema}','${TabName}')" > /dev/null 2>&1
fi
# Completed BUILD phase at 06/19/2008 06:28:05.446418.
if [[ "$a" = "Completed" && "$b" = "BUILD" ]]
then Type=$b
CompletedBUILDTimestamp=$db2diagTimestamp
db2 -x "update operations.db2diag_load_information set completed_timestamp = \
timestamp('${CompletedBUILDTimestamp}') where loadid = '${LOADID}' \
and type = '${Type}' and node = ${NODE}" > /dev/null 2>&1
fi
# CHANGE : STMM CFG DB EDWP02: "Pckcachesz" From: "4696" To: "4664"
if [[ "$a" = "CHANGE" && "$c" = "STMM" ]]
then CHANGESTMMTimestamp=$db2diagTimestamp
DATABASE=`echo $f | sed 's/://g'`
Type=`echo $g | sed 's/"//g'`
From=`echo $i | sed 's/"//g'`
To=`echo $l | sed 's/"//g'`
echo '"'$CHANGESTMMTimestamp'","'$Server'","'$Instance'","'$DATABASE'",'$NODE', \
"'$AUTHID'",'$PID',"'$APPHDL'","'$APPID'","'$Type'","FROM",\
'$From >> $FileName.stmm
echo '"'$CHANGESTMMTimestamp'","'$Server'","'$Instance'","'$DATABASE'",'$NODE', \
"'$AUTHID'",'$PID',"'$APPHDL'","'$APPID'","'$Type'","TO",\
'$To >> $FileName.stmm
fi
done
done
db2 -v connect to $Database | tee -a $OutputFile
db2 -v "import from $FileName.stmm of del messages $FileName.stmm.msg \
NP.insert_update into OPERATIONS.DB2DIAG_STMM_INFORMATION" | tee -a $OutputFile
echo "$ScriptName Vers: $ScriptVer ended on " `uname -n` on `date` | tee -a $OutputFile
echo "Output located in" | tee -a $OutputFile
ls $FileName*
The Tables
------------------------------------------------
-- DDL Statements for table "OPERATIONS"."DB2DIAG_LOAD_INFORMATION"
------------------------------------------------
CREATE TABLE "OPERATIONS"."DB2DIAG_LOAD_INFORMATION" (
"LOADID" CHAR(40) NOT NULL WITH DEFAULT ' ' ,
"TYPE" CHAR(5) NOT NULL WITH DEFAULT ' ' ,
"SERVER" CHAR(8) NOT NULL WITH DEFAULT ' ' ,
"INSTANCE" CHAR(8) NOT NULL WITH DEFAULT ' ' ,
"DATABASE" CHAR(8) NOT NULL WITH DEFAULT ' ' ,
"NODE" SMALLINT NOT NULL WITH DEFAULT 0 ,
"AUTHID" CHAR(8) NOT NULL WITH DEFAULT ' ' ,
"PID" BIGINT NOT NULL WITH DEFAULT 0 ,
"APPHDL" CHAR(8) NOT NULL WITH DEFAULT ' ' ,
"APPID" CHAR(31) NOT NULL WITH DEFAULT ' ' ,
"STARTING_TIMESTAMP" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP ,
"COMPLETED_TIMESTAMP" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP ,
"TABLENAME" VARCHAR(256) NOT NULL WITH DEFAULT '' ,
"TABSCHEMA" VARCHAR(128) NOT NULL WITH DEFAULT '' ,
"TABNAME" VARCHAR(128) NOT NULL WITH DEFAULT '' )
DISTRIBUTE BY HASH("LOADID")
IN "TSOPERATIONS01" INDEX IN IXOPERATIONS01 NOT LOGGED INITIALLY ;
-- DDL Statements for primary key on Table "OPERATIONS"."DB2DIAG_LOAD_INFORMATION"
ALTER TABLE "OPERATIONS"."DB2DIAG_LOAD_INFORMATION"
ADD CONSTRAINT "DB2DIAG_LOAD_IX01" PRIMARY KEY
("LOADID",
"TYPE",
"NODE");
------------------------------------------------
-- DDL Statements for table "OPERATIONS"."DB2DIAG_STMM_INFORMATION"
------------------------------------------------
CREATE TABLE "OPERATIONS"."DB2DIAG_STMM_INFORMATION" (
"CHANGE_TIMESTAMP" TIMESTAMP NOT NULL WITH DEFAULT ,
"SERVER" CHAR(8) NOT NULL WITH DEFAULT ,
"INSTANCE" CHAR(8) NOT NULL WITH DEFAULT ,
"DATABASE" CHAR(8) NOT NULL WITH DEFAULT ,
"NODE" SMALLINT NOT NULL WITH DEFAULT ,
"AUTHID" CHAR(8) NOT NULL WITH DEFAULT ,
"PID" BIGINT NOT NULL WITH DEFAULT ,
"APPHDL" CHAR(8) NOT NULL WITH DEFAULT ,
"APPID" CHAR(31) NOT NULL WITH DEFAULT ,
"TYPE" CHAR(40) NOT NULL WITH DEFAULT ,
"FROM_OR_TO" CHAR(4) NOT NULL WITH DEFAULT ,
"MEMORY_SIZE" BIGINT NOT NULL WITH DEFAULT )
DISTRIBUTE BY HASH("CHANGE_TIMESTAMP")
IN "TSOPERATIONS01" INDEX IN IXOPERATIONS01 NOT LOGGED INITIALLY ;
-- DDL Statements for primary key on Table "OPERATIONS"."DB2DIAG_STMM_INFORMATION"
ALTER TABLE "OPERATIONS"."DB2DIAG_STMM_INFORMATION"
ADD CONSTRAINT "DB2DIAG_STMM_IX01" PRIMARY KEY
("CHANGE_TIMESTAMP",
"SERVER",
"INSTANCE",
"DATABASE",
"NODE",
"AUTHID",
"PID",
"APPHDL",
"APPID",
"TYPE",
"FROM_OR_TO");
Example
If you run the script in foreground without passing it any parameters, it will echo display the parameters that the script uses. The first two parameters for the script are required so that the common reusable DBA script will know what DB2 instance and database to use. The third parameter is optional. You may pass any path that indicates the location of the DB2 diagnostic log files the script should read or the script will default to the DIAGPATH value by running the db2 get dbm cfg command to obtain this. The fourth parameter is optional and defaults to the db2diag.log file name. If you move your db2diag.log file to another path daily and rename it to db2diag_CCYYMMDDHHMMSS.log where CCYYMMDDHHMMSS is a date timestamp like I do then you may pass this value as the parameter and the script will only run against yesterday’s diagnostic log file. Otherwise, it will run against all db2diag*.log files in the path.
Moving your db2diag.log files to another path daily and renaming it to have a date timestamp in the name will help this script only process new information. This db2diag.log save technique can also help you keep your db2diag.log file to a manageable size daily and when you have to open a PMR with IBM and run db2support to send the db2diag.log file and other information, the files you have to send IBM will be smaller and easier for them to analyze. You can keep your DB2 diagnostic log file history or remove these files over time to prevent file systems from filling up.
db2diag_extract_information.ksh
Parm 1 Instance (required)
Parm 2 Database (required)
Parm 3 DB2 Diagnostic Log Path (optional) Defaults to DIAGPATH
May be a path where you put your db2diag_CCYYMMDDHHMMSS.log files
For example, "/dbawork/edwp02/db2dump"
Parm 4 DB2 Diagnostic Log File (optional) Defaults to db2diag*.log
May be db2diag_CCYYMMDD*.log
Parm 5 Script Output Directory Path (optional) Defaults to /dbawork/reports
When you run the script in foreground and pass it a DB2 instance id and database, you should see output displayed that is similar to the example shown below.
db2diag_extract_information.ksh edwp02 edwp02
db2diag_extract_information Vers: 06/10/2008 started on edwasp on Thu Nov 20 09:45:12 EST 2008
ls /db2home/edwp02/sqllib/db2dump/db2diag*.log
/db2home/edwp02/sqllib/db2dump/db2diag.log
Processing db2diag information in /db2home/edwp02/sqllib/db2dump/db2diag.log
connect to EDWP02
Database Connection Information
Database server = DB2/AIX64 9.1.4
SQL authorization ID = U0H005
Local database alias = EDWP02
import from /dbawork/reports/db2diag_extract_information_edwasp_edwp02_EDWP02_20081120094512.stmm
of del messages /dbawork/reports/db2diag_extract_information_edwasp_edwp02_EDWP02_20081120094512.stmm.msg
insert_update into OPERATIONS.DB2DIAG_STMM_INFORMATION
Number of rows read = 1386
Number of rows skipped = 0
Number of rows inserted = 1386
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 1386
db2diag_extract_information Vers: 06/10/2008 ended on edwasp on Thu Nov 20 09:48:25 EST 2008
Output located in
/dbawork/reports/db2diag_extract_information_edwasp_edwp02_EDWP02_20081120094512.ls
/dbawork/reports/db2diag_extract_information_edwasp_edwp02_EDWP02_20081120094512.stmm.msg
/dbawork/reports/db2diag_extract_information_edwasp_edwp02_EDWP02_20081120094512.stmm
/dbawork/reports/db2diag_extract_information_edwasp_edwp02_EDWP02_20081120094512.txt
Select Example
The output created by this script is stored in DB2 tables. You may develop SQL or a script that runs different types of SQL that will help you use this information to help you track table load and index build performance over time. This information can also be used to help identify what is running when and this may help you identify contention between different applications and users. You can also use this information to determine when DB2 is allowing a table load to incrementally add information to indexes and when it has switched over to completely rebuild indexes during a load. Here is one example of SQL that lists load information from yesterday.
select a.server,a.instance,a.database,a.node,a.authid,a.loadid,a.type,a.starting_timestamp,
a.completed_timestamp,char(a.tablename,40) as tablename,char(b.tbspace,20) as tablespace
from operations.db2diag_load_information a,syscat.tables b
where a.authid like '%%'
and a.tabschema=b.tabschema
and a.tabname=b.tabname
and ( date(a.starting_timestamp)= current date - 1 day or
date(a.completed_timestamp)= current date - 1 day or
current date -1 day between date(a.starting_timestamp)
and date(a.completed_timestamp)
)
order by a.starting_timestamp
SERVER INSTANCE DATABASE NODE AUTHID LOADID TYPE STARTING_TIMESTAMP COMPLETED_TIMESTAMP TABLENAME TABLESPACE
-------- -------- -------- ------ -------- ---------------------------------------- ----- -------------------------- -------------------------- ---------------------------------------- --------------------
edwasp edwp02 EDWP02 3 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.19.825346 2008-11-19-00.02.20.496011 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 9 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.19.829924 2008-11-19-00.02.20.528872 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 1 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.19.830849 2008-11-19-00.02.20.449521 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 6 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.19.839059 2008-11-19-00.02.20.492698 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 4 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.19.843122 2008-11-19-00.02.20.493764 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 10 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.19.844478 2008-11-19-00.02.20.499503 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 7 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.20.083580 2008-11-19-00.02.20.459874 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 2 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.20.195231 2008-11-19-00.02.20.454922 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 5 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.20.222706 2008-11-19-00.02.20.459483 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 8 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.20.222869 2008-11-19-00.02.20.868135 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 1 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.460441 2008-11-19-00.02.20.791962 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 2 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.489992 2008-11-19-00.02.20.661057 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 7 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.495294 2008-11-19-00.02.20.873008 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 5 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.496070 2008-11-19-00.02.20.742382 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 6 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.513766 2008-11-19-00.02.20.685750 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 4 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.526393 2008-11-19-00.02.20.675156 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 10 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.531163 2008-11-19-00.02.20.829576 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 9 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.537341 2008-11-19-00.02.20.713257 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 3 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.839878 2008-11-19-00.02.21.084649 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 8 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.883066 2008-11-19-00.02.21.143827 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 1 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.229639 2008-11-19-00.03.00.979805 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 10 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.238083 2008-11-19-00.03.00.983704 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 6 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.248511 2008-11-19-00.03.00.973224 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 4 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.250261 2008-11-19-00.03.00.971232 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 7 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.542755 2008-11-19-00.03.00.964926 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 3 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.549672 2008-11-19-00.03.00.967582 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 9 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.585876 2008-11-19-00.03.00.974125 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 2 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.660424 2008-11-19-00.03.00.981729 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 5 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.679167 2008-11-19-00.03.01.121790 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 8 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.680724 2008-11-19-00.03.00.966425 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 7 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.00.979796 2008-11-19-00.03.01.064096 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 8 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.00.980672 2008-11-19-00.03.01.091855 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 3 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.00.981723 2008-11-19-00.03.01.067041 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 4 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.00.983988 2008-11-19-00.03.01.064742 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 6 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.00.986718 2008-11-19-00.03.01.096628 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 9 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.00.989214 2008-11-19-00.03.01.085788 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 10 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.00.999050 2008-11-19-00.03.01.102833 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 5 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.01.132103 2008-11-19-00.03.01.191272 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 1 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.01.362555 2008-11-19-00.03.01.427459 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 2 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.01.472588 2008-11-19-00.03.01.542501 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 0 PP0342DL 5718288.2008-11-19-00.14.15.876530.0 LOAD 2008-11-19-00.14.16.169307 2008-11-19-00.14.16.305622 MDDB.TRNCTN_ALRT TSALR01
edwasp edwp02 EDWP02 0 PP0342DL 5718288.2008-11-19-00.14.15.876530.0 BUILD 2008-11-19-00.14.16.312567 2008-11-19-00.14.16.569363 MDDB.TRNCTN_ALRT TSALR01
edwasp edwp02 EDWP02 0 PP0342DL 2039862.2008-11-19-00.14.57.185249.0 LOAD 2008-11-19-00.14.57.360736 2008-11-19-00.14.57.417611 MDDB.PRCS_ALRT TSALR01
edwasp edwp02 EDWP02 0 PP0342DL 2039862.2008-11-19-00.14.57.185249.0 BUILD 2008-11-19-00.14.57.424661 2008-11-19-00.14.57.442925 MDDB.PRCS_ALRT TSALR01
edwasp edwp02 EDWP02 0 PP0341DL 4551084.2008-11-19-03.34.11.855921.0 LOAD 2008-11-19-03.34.12.694610 2008-11-19-03.34.13.120171 MDDB.TRNCTN_ALRT TSALR01
edwasp edwp02 EDWP02 0 PP0341DL 4551084.2008-11-19-03.34.11.855921.0 BUILD 2008-11-19-03.34.13.138966 2008-11-19-03.34.13.538798 MDDB.TRNCTN_ALRT TSALR01
edwasp edwp02 EDWP02 0 PP0341DL 6910400.2008-11-19-03.35.17.182007.0 LOAD 2008-11-19-03.35.17.456812 2008-11-19-03.35.17.551005 MDDB.PRCS_ALRT TSALR01
edwasp edwp02 EDWP02 0 PP0341DL 6910400.2008-11-19-03.35.17.182007.0 BUILD 2008-11-19-03.35.17.575893 2008-11-19-03.35.17.605481 MDDB.PRCS_ALRT TSALR01
You may want to develop SQL select statements to run against the OPERATIONS.DB2DIAG_STMM_INFORMATION table to gain insight into what DB2 automatic memory management is doing as well.
Conclusion
If you do not have sophisticated database monitoring software that captures information about DB2 table loads and index builds, you can capture this yourself from the DB2 diagnostic log files by using this script. This valuable source of information can be used by a DBA as evidence to show when table loads and index builds have run and how long they take to process. If the load step is just one part of a job then you will know how much time the database is spending on the load and how much time the job is spending doing other things. If you develop a dash board for application teams, users and management that gives them visibility into this information then they can monitor load history and performance over time and gain a better understanding about what is running and when.
If you have any questions related to this article, feel free to contact me, Mark Mulligan, at mark.mulligan.sr@sbcglobal.net and I will be glad to try to answer your questions.
About the author
Mark Mulligan is a DBA living in the Dallas-Fort Worth area of Texas. He has a Bachelor of Science Degree in Business Administration from Merrimack College in North Andover, Massachusetts, and 30 years of experience working with computer software design, development, maintenance, enhancement and support. He has 10 years of experience working with DB2 on servers running UNIX-like operating systems and 15 years of experience working with DB2 on Z/OS. Mark can be contacted at mark.mulligan.sr@sbcglobal.net.
DB2 Check Pending Script
Submitted by Vikram Khatri on Fri, 2008-11-14 22:27.Thanks to Max Petrenko of DB2 Toronto Lab for sharing a very useful script to remove check pending status from the DB2 tables after LOAD or other operations. It is easy to generate a check pending script, but the importance of this script is that it builds the sequence in such a fashion that the dependencies are taken care automatically.
A simple approach to remove check pending
Generate script using a simple SELECT statement as shown below:
CONNECT TO TESTDB; SET INTEGRITY FOR "VIKRAM"."DEBUG_TABLE" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."DESTINATION" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."CLASSES" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."CALL_STACKS" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."ERRORS" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."EXCEPTION_TABLE" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."LOG_TABLE" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."MAJOR_STATS" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."SOURCE" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."ERROR_STACKS" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."REGISTERED_STUDENTS" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."STUDENTS" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."RS_AUDIT" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."TABNUM" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."TEMP_TABLE" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."ROOMS" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."TAB1" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."TAB3" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."TMP" IMMEDIATE CHECKED; SET INTEGRITY FOR "VIKRAM"."TAB2" IMMEDIATE CHECKED; TERMINATE;
But, the problem with above approach is that the order of the tables is not as per the dependencies with a result that you may get this error:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL3608N Cannot check a dependent table "VIKRAM.REGISTERED_STUDENTS" using the SET INTEGRITY statement while the parent table or underlying table "VIKRAM.STUDENTS" is in the Set Integrity Pending state or if it will be put into the Set Integrity Pending state by the SET INTEGRITY statement. SQLSTATE=428A8
You have to run above script iteratively few times to remove tables from check pending status. This is definitely cumbersome.
A more elegant approach
db2 connect to sample db2 -tx +w "with gen(tabname, seq) as( select rtrim(tabschema) || '.' || rtrim(tabname) as tabname, row_number() over (partition by status) as seq from syscat.tables WHERE status='C' ),r(a, seq1) as (select CAST(tabname as VARCHAR(3900)), seq from gen where seq=1 union all select r.a || ','|| rtrim(gen.tabname), gen.seq from gen , r where (r.seq1+1)=gen.seq ), r1 as (select a, seq1 from r) select 'SET INTEGRITY FOR ' || a || ' IMMEDIATE CHECKED;' from r1 where seq1=(select max(seq1) from r1)" > db2FixCheckPending.sql db2 -tvf db2FixCheckPending.sql
A sample output:
SET INTEGRITY FOR VIKRAM.ERROR_STACKS,VIKRAM.CLASSES,VIKRAM.CALL_STACKS,VIKRAM.ERRORS,VIKRAM.REGISTERED_STUDENTS, VIKRAM.ROOMS,VIKRAM.STUDENTS IMMEDIATE CHECKED;
The order of the tables in above script is as per dependencies and the above single statement will run check pending command in the right order.
The only limitation is the size of the SET command - based on this script it cannot be larger that 3900 characters. You can increase the size up to 30,000 characters, but in this case you would need to have System Temporary Tablespace of 32K, which is not available by default.
You can download the script from here
DB2 Backup Environment Script as part of a Disaster Recovery Plan
Submitted by Mark Mulligan on Fri, 2008-11-14 15:37.Mark Mulligan – (mark.mulligan.sr@sbcglobal.net) DB2 DBA
01 November 2008
A critical part of any database disaster recovery plan is to make sure that the environment surrounding the database is the way it was before the disaster in order to provide the same level of performance and level of service to users after a recovery. This article explains a process to backup UNIX and DB2 environment information that contains important information that can be used during a disaster recovery or operational recovery. An example of a KORN shell script is provided to help you keep this information so it will be available when you need it.
Introduction
If you ever need to recover a DB2 environment from a disaster or serious operational system problem and want the database to perform like it did before the recovery, information about the environment is the fastest, most thorough way to accomplish your goal. This information is critical to your success in addition to restoring DB2 database backups that are a part of your disaster recovery plan.
This article describes the information you need to have available and a script that will help accomplish this and tells you step-by-step, how to use them.
Pros and cons
The advantage of having UNIX and DB2 environment information backed up and stored off site with your database backups is that you will have the information you need to verify that the environment and the database are completely restored after a disaster and that the database will perform and provide the same level of service after the recovery.
If you do not have UNIX and DB2 environment information backed up and stored off site with your database backups, then you risk having performance problems and unreliable service from a database after a recovery from a disaster.
Description
Here is a list of information that is helpful to have in addition to a DB2 database backup that can be used to verify an environment has been recovered or rebuilt in the same way before you restore your database backup after a disaster or severe operational problem.
oslevel
This information can be used to verify that the same operating system level is available in support of the recovery.
lslpp -h | grep -vp bos | grep -vp device | grep -vp X11
This above command backs up a list of all of the software installed on the server and can be used to verify that the operating system and programs installed on the server are as they were before the disaster. (This can be accomplished with different commands on different operating systems.)
db2level
This information can be used to verify that the same DB2 level is available in support of a recovery.
cat /etc/services
A backup of this can be used to ensure that the TCP/IP ports used by a DB2 instance are the same as before. Ports used to support partitioned database environments and tools like the high performance unload can be confirmed as well.
df –g
A backup of this UNIX command can be used to verify the file systems, space allocations and mount point paths restored or created after a disaster. This must be verified before you restore a database backup to ensure that the restore will be successful and that the database will fit in the file systems on the server.
mount A backup of this information can be used to verify that file systems use the same mount options that were used before a database is restored. You can verify that jfs, jfs2, cio, rbr, rbw and other mount options match so that you will experience similar operating system I/O performance after recovering from a disaster.
id instance Use information backed up by this command to verify that the instance id is set up with the correct operating system groups.
ulimit –a
Output from this command run for DB2 instance id’s can be used to verify that the id is set up the same way as before.
crontab –l
List output from the crontab command will save a UNIX crontab schedule if you use that to run jobs under a DB2 instance id. If you use a different scheduler to run jobs then you should have those schedules backed up as part of your disaster recovery plan.
cp .profile
A copy of the .profile file for a DB2 instance id’s can be copied for reuse after a disaster.
userprofile A copy of the DB2 userprofile for a DB2 instance id can be copied to reuse after a disaster if you customize your db2profile environment with environment variables in the sqllib userprofile file that is shipped with DB2.
cp db2.conf
A copy of the Veritas NETBACKUP db2.conf backup configuration file for a DB2 instance can be restored to reestablish your DB2 backup and restore environment. (Change this to commands used to backup TSM, HP Data Protector or other types of backup configuration information depending on the software you use for backups.)
db2licm -l This information can be used to verify that you have applied the correct license information for a DB2 instance in the event that you have to recreate an instance after a disaster.
db2 get admin cfg
The output from this DB2 command can be used to reestablish the DB2 administration configuration during a restore.
db2cfexp
This DB2 command will export configuration information that can be imported to completely restore DB2 environment information like a DB2 database directory, a DB2 node directory and other DB2 configuration information. The db2cfimp filename command can be used to import this information and help recover a large part of a DB2 environment. This should usually be run before restoring a database backup.
db2 list node directory The output from this DB2 command can be used to verify that a DB2 node directory is like it was before a problem requiring a restore or rebuild.
db2 list database directory
The output from this DB2 command can be used to verify the DB2 database directory.
db2set -all
Use this DB2 command information to verify the DB2 registry settings for a DB2 instance as part of your recovery steps.
db2 get dbm cfg
Use this output to verify that the DB2 instance configuration is what it was before the operational problem or disaster.
db2 get db cfg for DATABASE_NAME
A backup of the output from this command can be used to verify that DB2 database configurations are restored correctly. (db2_all “db2 get db cfg for DATABASE_NAME” may be used for partitioned databases.)
Environment
The script in this article was tested on a server running the AIX operating system and DB2 versions 8, 9.1 and 9.5. The KORN shell script should work on any server that has a UNIX-like operating system that supports these types of scripts. (Linux and Linux/390 come to mind.) This script is designed to run in a native UNIX environment and is not intended to run under the DB2 Script Center Client Window. This script should be run under a DB2 instance id or scheduled to run on a daily basis for each DB2 instance id. This script is designed to run against stand alone DB2 environments or DB2 partitioned database environments though it must be run on each server that is part of a DB2 partitioned environment if you want to have complete coverage.
Before you get started, a couple of words of caution:
- Practice running the script and verify the results.
- Make sure you have a DB2 database backup solution implemented in addition to this DB2 environment backup solution. The combination of the two will help provide what you need to recover from an operational problem or a disaster.
- Storing DB2 database backups and DB2 environment backup information off site can help ensure that the information you need will be available after a disaster.
CRONTAB schedule example
00 00 * * * /db2util/scripts/db2backup_environment.ksh db2inst1 >/dev/null 2>&1
The script
#!/bin/ksh
#-----------------------------------------------------------------------
# Program : db2backup_environment.ksh
# Description : This script backs up DB2 environment information
# : that is important to have for disaster recovery
# : purposes.
# Author : Mark Mulligan
# Date : 05/18/2001
#-----------------------------------------------------------------------
ScriptVer='05/18/2001'
ScriptName='db2backup_environment'
Server=$(hostname)
if [ "$1" = "" ]
then echo ""
echo "Parm 1 Instance (required)."
echo "Parm 2 Script Output Directory Path (optional) Defaults to /dbawork/reports"
echo "Parm 3 Disaster Recovery Directory Path (optional) Defaults to /dbawork/reports/DISASTER_RECOVERY_INFO"
echo ""
return 8
else Instance=`echo $1 | tr '[A-Z]' '[a-z]'`
fi
if [ "$2" = "" ]
then OutputDir="/dbawork/reports"
else OutputDir="${2}"
fi
if [ -d $OutputDir ]
then :
else echo "OutputDir $OutputDir must exist before you run this script."
echo "Run mkdir commands to create this directory path."
return 8
fi
if [ "$3" = "" ]
then DisasterRecoveryPath="/dbawork/reports/DISASTER_RECOVERY_INFO"
else OutputDir="${3}"
fi
if [ -d $DisasterRecoveryPath ]
then :
else echo "DisasterRecoveryPath $DisasterRecoveryPath must exist before you run this script."
echo "Run mkdir commands to create this directory path."
return 8
fi
. $HOME/sqllib/db2profile
Datetime="`date +%Y%m%d%H%M%S`"
FileName=${OutputDir}/${ScriptName}_${Server}_${Instance}_${Datetime}
OutputFile=${FileName}.txt
DisasterRecoveryFile=${DisasterRecoveryPath}/${ScriptName}_${Server}_${Instance}_${Datetime}
echo "$ScriptName Vers: $ScriptVer started on " `uname -n` on `date` | tee $OutputFile
# backup the oslevel
echo "oslevel > ${DisasterRecoveryFile}.oslevel.txt" | tee -a $OutputFile
oslevel > ${DisasterRecoveryFile}.oslevel.txt
# list software installed on the server
echo "lslpp -h | grep -vp bos | grep -vp device | grep -vp X11 > ${DisasterRecoveryFile}.lslpp.txt" | tee -a $OutputFile
lslpp -h | grep -vp bos | grep -vp device | grep -vp X11 > ${DisasterRecoveryFile}.lslpp.txt
# backup db2level
echo "db2level > ${DisasterRecoveryFile}.db2level.txt" | tee -a $OutputFile
db2level > ${DisasterRecoveryFile}.db2level.txt
# copy /etc/services file that contains DB2 TCP/IP information
if [ -f /etc/services ]
then echo "cp /etc/services ${DisasterRecoveryFile}.services" | tee -a $OutputFile
cp /etc/services ${DisasterRecoveryFile}.services
fi
# display file system information
echo "df -g > ${DisasterRecoveryFile}.df_g.txt" | tee -a $OutputFile
df -g > ${DisasterRecoveryFile}.df_g.txt
# backup mount information
echo "mount > ${DisasterRecoveryFile}.mount.txt" | tee -a $OutputFile
mount > ${DisasterRecoveryFile}.mount.txt
# backup id information for the instance id
echo "id $Instance > ${DisasterRecoveryFile}.id.txt" | tee -a $OutputFile
id $Instance > ${DisasterRecoveryFile}.id.txt
# backup ulimit settings for the instance id
echo "ulimit -a > ${DisasterRecoveryFile}.ulimit.txt" | tee -a $OutputFile
ulimit -a > ${DisasterRecoveryFile}.ulimit.txt
# backup crontab
echo "crontab -l > ${DisasterRecoveryFile}.crontab.txt" | tee -a $OutputFile
crontab -l > ${DisasterRecoveryFile}.crontab.txt
# copy instance profile
if [ -f $HOME/.profile ]
then echo "cp $HOME/.profile ${DisasterRecoveryFile}.profile" | tee -a $OutputFile
cp $HOME/.profile ${DisasterRecoveryFile}.profile
fi
# copy userprofile
if [ -f $HOME/sqllib/userprofile ]
then echo "cp $HOME/sqllib/userprofile ${DisasterRecoveryFile}.userprofile" | tee -a $OutputFile
cp $HOME/sqllib/userprofile ${DisasterRecoveryFile}.userprofile
fi
# copy Netbackup configuration
if [ -f $HOME/db2.conf ]
then echo "cp $HOME/db2.conf ${DisasterRecoveryFile}.db2.conf" | tee -a $OutputFile
cp $HOME/db2.conf ${DisasterRecoveryFile}.db2.conf
fi
# backup DB2 license information
echo "db2licm -l > ${DisasterRecoveryFile}.license.information" | tee -a $OutputFile
db2licm -l > ${DisasterRecoveryFile}.db2licm.license.information
cp $HOME/sqllib/adm/*.lic $DisasterRecoveryPath/. >/dev/null 2>&1
# backup DB2 administration server configuration.
echo "db2 get admin cfg > ${DisasterRecoveryFile}.admin.cfg" | tee -a $OutputFile
db2 get admin cfg > ${DisasterRecoveryFile}.admin.cfg
# export database directory, node directory, instance configuration and other DB2 environment information
echo "db2cfexp ${DisasterRecoveryFile}_db2cfexp.bak backup" | tee -a $OutputFile
db2cfexp ${DisasterRecoveryFile}_db2cfexp.bak backup
echo "db2 list node directory > ${DisasterRecoveryFile}.db2.list.node.directory" | tee -a $OutputFile
db2 list node directory > ${DisasterRecoveryFile}.db2.list.node.directory
echo "db2 list database directory > ${DisasterRecoveryFile}.db2.list.database.directory" | tee -a $OutputFile
db2 list database directory > ${DisasterRecoveryFile}.db2.list.database.directory
# list DB2 environment registry variable settings
echo "db2set -all > ${DisasterRecoveryFile}.db2set" | tee -a $OutputFile
db2set -all > ${DisasterRecoveryFile}.db2set
# save off instance configuration for comparison purposes even though this can be imported
# using db2cfimp with the db2cfexp.bak file created above
echo "db2 get dbm cfg > ${DisasterRecoveryFile}.dbm.cfg" | tee -a $OutputFile
db2 get dbm cfg > ${DisasterRecoveryFile}.dbm.cfg
# copy db2nodes.cfg if it exists
if [ -f $HOME/sqllib/db2nodes.cfg ]
then echo "cp $HOME/sqllib/db2nodes.cfg ${DisasterRecoveryFile}.db2nodes.cfg" | tee -a $OutputFile
cp $HOME/sqllib/db2nodes.cfg ${DisasterRecoveryFile}.db2nodes.cfg
fi
db2 list database directory > $FileName.dat
cat $FileName.dat | while read column1 column2 column3 column4 column5 column6
do
if [[ "$column1" = "Database" && "$column2" = "alias" ]]
then export Database=$column4
fi
if [[ "$column1" = "Directory" && "$column5" = "Indirect" ]]
then if [ -f $HOME/sqllib/db2nodes.cfg ]
then cat $HOME/sqllib/db2nodes.cfg | while read NodeNumber HostName LogicalPort NetName
do
echo "PARTITION ${NodeNumber}: db2 -v get db cfg for ${Database} show detail
>> ${DisasterRecoveryFile}_${Database}.db.cfg" | tee -a $OutputFile
echo "DB2NODE=$NodeNumber" >> ${DisasterRecoveryFile}_${Database}.db.cfg
export DB2NODE=$NodeNumber
db2 -v terminate >> ${DisasterRecoveryFile}_${Database}.db.cfg
db2 -v connect to $Database >> ${DisasterRecoveryFile}_${Database}.db.cfg
db2 -v get db cfg for ${Database} show detail >> ${DisasterRecoveryFile}_${Database}.db.cfg
done
else echo "db2 -v get db cfg for ${Database} show detail >> ${DisasterRecoveryFile}_${Database}.db.cfg" | tee -a $OutputFile
db2 -v terminate > ${DisasterRecoveryFile}_${Database}.db.cfg
db2 -v connect to $Database > ${DisasterRecoveryFile}_${Database}.db.cfg
db2 -v get db cfg for ${Database} show detail > ${DisasterRecoveryFile}_${Database}.db.cfg
fi
fi
done
echo "$ScriptName Vers: $ScriptVer ended on " `uname -n` on `date` | tee -a $OutputFile
echo "Output located in $OutputFile" | tee -a $OutputFile
Example
If you run the script in foreground without passing it any parameters, it will echo display the parameters that the script uses. The script output directory path and the disaster recovery directory path must exist before the script will run the commands to create information about your DB2 environment that can be used during a disaster recovery or recovery from a severe operational problem.
db2backup_environment.ksh
Parm 1 Instance (required). Parm 2 Script Output Directory Path (optional) Defaults to /dbawork/reports Parm 3 Disaster Recovery Directory Path (optional) Defaults to /dbawork/reports/DISASTER_RECOVERY_INFO
When you run the script under a DB2 instance id in foreground and pass it the DB2 instance id you should see output displayed that is similar to the example shown below.
/db2util/scripts/db2backup_environment.ksh db2inst1 db2backup_environment Vers: 05/18/2001 started on edwasu on Fri Nov 14 08:57:39 EST 2008 oslevel > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.oslevel.txt lslpp -h | grep -vp bos | grep -vp device | grep -vp X11 > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.lslpp.txt db2level > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2level.txt cp /etc/services /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.services df -g > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.df_g.txt mount > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.mount.txt id db2inst1 > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.id.txt ulimit -a > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.ulimit.txt crontab -l > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.crontab.txt cp /db2home/db2inst1/.profile /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.profile cp /db2home/db2inst1/sqllib/userprofile /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.userprofile cp /db2home/db2inst1/db2.conf /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2.conf db2licm -l > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.license.information db2 get admin cfg > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.admin.cfg db2cfexp /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739_db2cfexp.bak backup db2 list node directory > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2.list.node.directory db2 list database directory > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2.list.database.directory db2set -all > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2set db2 get dbm cfg > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.dbm.cfg cp /db2home/db2inst1/sqllib/db2nodes.cfg /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2nodes.cfg PARTITION 0: db2 -v get db cfg for PROD01 show detail >> /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739_PROD01.db.cfg PARTITION 1: db2 -v get db cfg for PROD01 show detail >> /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739_PROD01.db.cfg PARTITION 2: db2 -v get db cfg for PROD01 show detail >> /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739_PROD01.db.cfg PARTITION 3: db2 -v get db cfg for PROD01 show detail >> /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739_PROD01.db.cfg PARTITION 4: db2 -v get db cfg for PROD01 show detail >> /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739_PROD01.db.cfg db2backup_environment Vers: 05/18/2001 ended on edwasu on Fri Nov 14 08:57:48 EST 2008 Output located in /dbawork/reports/db2backup_environment_edwasu_db2inst1_20081114085739.txt
List Example
The output created by this script can be listed for a particular day to see the DB2 backup environment information as of that day. Cut and paste the file to a cat command to see the contents.
ls /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739* /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.admin.cfg /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.crontab.txt /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2.conf /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2.list.database.directory /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2.list.node.directory /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2level.txt /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2licm.license.information /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2nodes.cfg /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2set /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.dbm.cfg /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.df_g.txt /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.id.txt /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.lslpp.txt /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.mount.txt /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.oslevel.txt /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.profile /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.services /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.ulimit.txt /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.userprofile /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739_PROD01.db.cfg /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739_db2cfexp.bak
Conclusion
A DB2 Backup Environment Script can be an integral part of your disaster recover plan to protect the valuable information assets in the databases you support. It can help give you information about the operating system, software installed on that system and database environments that you can use to verify that the restored database and environment will operate in the same way that it did before the disaster.
If you have any questions related to this article, feel free to contact me, Mark Mulligan, at mark.mulligan.sr@sbcglobal.net and I will be glad to help in any way that I am able
About the author
Mark Mulligan is a DBA living in the Dallas-Fort Worth area of Texas. He has a Bachelor of Science Degree in Business Administration from Merrimack College in North Andover, Massachusetts, and 30 years of experience working with computer software design, development, maintenance, enhancement and support. He has 10 years of experience working with DB2 on servers running UNIX-like operating systems and 15 years of experience working with DB2 on Z/OS. Mark can be contacted at mark.mulligan.sr@sbcglobal.net.
How to concatenate rows together?
Submitted by Vikram Khatri on Tue, 2008-08-12 00:32.Say, you want to concatenate all rows of a column and show it as a single row in the same fashion aggregate function works.
$ db2 "create table t1(line# int not null, word varchar(20))"
$ db2 "insert into t1 values(1,'The'),(1,'horse'),(1,'is'),(1,'white')"
$ db2 "insert into t1 values(2,'The'),(2,'quick'),(2,'brown'),
(2,'fox'),(2,'jumps'),(2,'over'),(2,'the'),(2,'dog')"
$ db2 "select * from t1"
LINE# WORD
----------- --------------------
1 The
1 horse
1 is
1 white
2 The
2 quick
2 brown
2 fox
2 jumps
2 over
2 the
2 dog
But, you want this to be in a single row. For example:
1 The horse is white 2 The quick brown fox jumps over the dog
SQL Server or Sybase allows you to concatenate string together. For example, you could use the following construct to get string concatenation.
declare @phrase varchar(1024) set @phrase='' select @phrase= word + @phrase from t1
People can debate this forever that why are you doing this processing at the server level against the wishes of RDBMS engine. Normally, the front end should have logic to do this processing. It is like destroying 1st normal form with a concatenated list.
Again, we do always compare things without rationalization. Read the book Predictably Irrational if you want to know that how humans beings are irrational by nature.
Yes, we can do same in DB2 in several different fashions. For example:
- Use XMLAGG Function
- Use a C UDF
- Use recursive query
- Use a SQL UDF by opening a cursor
Method - 1 : use XMLAGG function
SELECT line#, replace(replace(xml2clob(xmlagg(xmlelement(NAME a, word))),'<A>',''),'</A>',' ') FROM t1 GROUP BY line#
Here, we use XML capability of DB2 to turn a data type to XML using XMLELEMENT. Use XMLAGG to aggregate the values and use XML2CLOB to view the data. We use REPLACE to trim XML tag. The output is as follows:
1 The horse is white 2 The quick brown fox jumps over the dog
Method - 2 : C UDF to process all rows (joinstr.c)
#include <stdio.h>
#include <string.h>
#include <stdarg.h>
#include <sqludf.h>
#define STR_LEN 1024
struct SCRATCHDATA
{
int changed;
char str[STR_LEN];
};
void SQL_API_FN joinstr(SQLUDF_VARCHAR *inStr, SQLUDF_INTEGER *changed, SQLUDF_VARCHAR *outStr,
SQLUDF_SMALLINT *inStrIND, SQLUDF_INTEGER *changedIND, SQLUDF_SMALLINT *outStrIND, SQLUDF_TRAIL_ARGS_ALL)
{
struct SCRATCHDATA *sp;
sp = (struct SCRATCHDATA *) SQLUDF_SCRAT->data;
switch (SQLUDF_CALLT)
{
case SQLUDF_FIRST_CALL:
memset(sp,0,sizeof(struct SCRATCHDATA));
if(*inStrIND >= 0)
{
strcpy(sp->str, inStr);
if (*changedIND >= 0)
sp->changed = *changed;
}
break;
case SQLUDF_NORMAL_CALL:
if(*inStrIND >= 0)
{
if (*changedIND >= 0)
{
if (*changed != sp->changed)
memset(sp,0,sizeof(struct SCRATCHDATA));
else
strcat(sp->str, " ");
strcat(sp->str, inStr);
sp->changed = *changed;
}
}
break;
case SQLUDF_FINAL_CALL:
break;
}
strcpy(outStr, sp->str);
*outStrIND = 0;
}
joinstr.exp
joinstr
makefile
DB2PATH=${HOME}/sqllib
BLDRTN=./bldrtn
ALIAS=sample
UID=
PWD=
COPY=cp
ERASE=rm -f
all : \
srv
srv : \
rtn
rtn : \
joinstr
clean :
$(ERASE) *.o
#****************************************************************************
# 2h - make cleanall
#****************************************************************************
cleanall : \
clean
$(ERASE) joinstr
$(ERASE) $(DB2PATH)/function/joinstr*
joinstr : joinstr.c
$(BLDRTN) joinstr $(ALIAS)
./udfcat $(ALIAS) > udfcat.log
udfcat - Catalog the UDF
#! /bin/ksh
export ${FENCED:="NOT FENCED THREADSAFE"}
export ${SHLIBNAME:="joinstr"}
TOK=$(date +"%y%m%d%H%M%S")
export SHLIBNAME=joinstr$TOK
rm -f ~/sqllib/function/joinstr*
cp -f joinstr ~/sqllib/function/$SHLIBNAME
db2 -tv <<!EOF
CONNECT TO $1;
DROP FUNCTION JOIN_STR;
CREATE FUNCTION JOIN_STR
(
IN_STR VARCHAR(1024),
LINE_NO INTEGER
)
RETURNS VARCHAR (1024)
EXTERNAL NAME '${SHLIBNAME}!joinstr'
SPECIFIC JOINSTR
LANGUAGE C
PARAMETER STYLE DB2SQL
${FENCED}
RETURNS NULL ON NULL INPUT
NOT DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
SCRATCHPAD 2000
FINAL CALL
DISALLOW PARALLEL;
How to compile?
Unzip joinstr.tar.gz all files into a directory. Copy bldrtn from ~/sqllib/samples/c to your current directory and run make.
How to run?
db2 "select line#, max(join_str(word,line#)) from t1 group by line#"
We use MAX function to do the aggregation but the actual work is done by the C UDF to join words together.
Method - 3: SQL UDF to join words for a given line
CREATE FUNCTION get_str(v_line# INTEGER)
LANGUAGE SQL
RETURNS VARCHAR(1024)
BEGIN ATOMIC
DECLARE str VARCHAR(1024);
SET str = '';
loop1: FOR row AS (SELECT word FROM t1 where line# = v_line#)
DO
IF row.word IS NOT NULL THEN
SET str = str || row.word || ' ';
END IF;
END FOR loop1;
RETURN str;
END
@
db2 "SELECT line#, get_str(line#) FROM t1 GROUP BY line#"
Method - 4 : Recursive SQL to join words for a line
WITH temp1(line#, word, all_words, cnt) as (SELECT min(line#), min(word), VARCHAR(min(word),50),SMALLINT(1) FROM t1 a group by a.line# UNION ALL SELECT a.line#, a.word, b.all_words||' '||a.word,SMALLINT(b.cnt+1) FROM t1 a, temp1 b WHERE a.line# = b.line# AND a.word > b.word AND a.word = (select min(c.word) from t1 c where c.line# = b.line# and c.word > b.word) ) SELECT d.line#, all_words FROM temp1 d where cnt = (SELECT max(cnt) FROM temp1 e where e.line# = d.line#); 1 The horse is white 2 The brown dog fox jumps over quick the
The question comes - which one of the above should I use?
If you are averse to C and hate to take burden of compiling C UDF as shown, use Method - 1 XMLAGG function. C UDF is light weight and uses scratchpad as you are going through the rows. It will give you the best performance. The recursive SQL is a good example but it could be slow.
Speed up MQT - Tips
Submitted by Vikram Khatri on Fri, 2008-07-11 13:50.Edin I. Aganagic shares his tips on how to increase incremental refresh/maintenance performance of MQTs in DB2.
- Create an index on the GROUP BY columns. It helps speed up MQT look-ups needed to decide whether existing row needs to be changed, new one inserted, or last one deleted
- If not aggregating, create the index on the set of columns that form a unique key usually by concatenating the unique key columns of the individual base tables.
- If possible, define the base table columns used in the GROUP BY clause of REFRESH IMMEDIATE MQTs as NOT NULL makes finding matching rows in the MQT easier
- Define RI relationships wherever applicable allows various loss-less join specific optimizations (even not enforced)
- Always keep the base table and MQT statistics up-to-date and it helps the optimizer choose an optimal access plan
DB2 ODBC Driver double quotes
Submitted by Vikram Khatri on Tue, 2008-06-24 15:50.If you are migrating your ODBC related code from one database to DB2, you might run into an issue of single quote vs. double quote issue.
For example: Your ODBC code might issue a call to a Stored Procedure using double quote as shown below:
CALL TESTSP("PARAM1","PARAM2")
Your previous database might allow you to pass parameters using double quotes but this will fail in DB2 since literals used in double quotes are treated as table name or column name in DB2 and hence above code will not work.
What if, if you have thousands of such statements and there will be some conversion effort for such statements. There is a workaround to this problem but you will be on your own to use this workaround as this is not documented and you are not likely to get any support for this from DB2.
It is strongly advised to be very careful if attempting to use either of these work around. They are not recommended for general implementation and may cause undesirable behavior in some cases.
You can add following entries in db2cli.ini file for this to take effect.
PATCH1 = 2048
This patch1 will replace all double-quotes with single-quotes. This patch is used to help port SQL that has been written using literals with double-quotes instead of the DB2 required single quotes.
PATCH2=62
This patch2 does a similar mapping as PATCH1=2048 but avoids some specific situations where DB2 thinks that the quotes are around an SQL object (which needs to remain double-quoted).
Again a no-nonsense disclosure: Use it at your own risk as this is not supported officially.
C Table UDF to join flat file with your SQL
Submitted by Vikram Khatri on Thu, 2008-06-12 05:33.Sometimes, it may be necessary to expose data in your flat file with the relational data. There might be some situations where there is no option to load that data in a table. But still, you need a way to use this flat file data in your SQL statements.
DB2 provides the concept of a table function that you can write. Here is one C table UDF example that reads a file and returns that data through your SQL statement.
Download sample code ufd.zip
udfreadfile.c
#include <stdio.h>
#include <stdarg.h>
#include <sqludf.h>
#define DEBUG 0
struct SCRATCHDATA
{
long idx;
FILE *p;
};
void hardw(char *msg, ...)
{
FILE *p;
char name[500];
va_list argp;
if (DEBUG)
{
strcpy(name, getenv("HOME"));
if (name == NULL)
{
strcpy(name, "/tmp/hard.txt");
} else
{
strcat(name, "/sqllib/hard.txt");
}
p = fopen(name, "a");
if (p == NULL)
{
p = fopen("/tmp/hard.txt", "a");
}
if (p != NULL)
{
va_start(argp, msg);
vfprintf(p, msg, argp);
va_end(argp);
fprintf(p, "\n");
fflush( p);
fclose(p);
}
}
}
void SQL_API_FN readFile(SQLUDF_VARCHAR *fileName,
SQLUDF_VARCHAR *cm15,
SQLUDF_NULLIND *fileNameInd,
SQLUDF_NULLIND *cm15IND,
SQLUDF_TRAIL_ARGS_ALL)
{
char myData[100];
long i = 0;
char errMsg[1000];
struct SCRATCHDATA *sp;
sp = (struct SCRATCHDATA *) SQLUDF_SCRAT->data;
switch (SQLUDF_CALLT)
{
case SQLUDF_TF_FIRST:
hardw("in SQLUDF_TF_FIRST ******");
if (*fileNameInd == -1)
{
strcpy( SQLUDF_STATE, "38777");
sprintf (errMsg, "Null file name.");
strcpy( SQLUDF_MSGTX, errMsg);
break;
}
sp->p = fopen(fileName, "r");
if (sp->p == NULL)
{
strcpy( SQLUDF_STATE, "38777");
sprintf (errMsg, "Given file can not be opened", fileName);
strcpy( SQLUDF_MSGTX, errMsg);
hardw("File '%s'can not be opened", fileName);
break;
}
sp->idx = 0;
case SQLUDF_TF_OPEN:
hardw("in SQLUDF_TF_OPEN ******");
break;
case SQLUDF_TF_FETCH:
hardw("in SQLUDF_TF_FETCH %ld ******", sp->idx);
if (fgets(myData, 100, sp->p) == NULL)
{
strcpy( SQLUDF_STATE, "02000");
break;
}
myData[strlen(myData)-1] = '\0';
strcpy(cm15, myData);
*cm15IND = 0;
sp->idx++;
break;
case SQLUDF_TF_CLOSE:
hardw("in SQLUDF_TF_CLOSE ******");
fclose(sp->p);
break;
case SQLUDF_TF_FINAL:
/* close the file */
hardw("in SQLUDF_TF_FINAL ******");
sp->idx = 0;
break;
}
}
udfreadfile.exp
readFile
How to catalog Table UDF (udfcat)
The catalog definition to register C table UDF in DB2 catalog is given below:
#! /bin/ksh
export ${FENCED:="FENCED NOT THREADSAFE"}
export ${SHLIBNAME:="udfreadfile"}
TOK=$(date +"%y%m%d%H%M%S")
export SHLIBNAME=udfreadfile$TOK
rm -f ~/sqllib/function/udfreadfile*
cp -f udfreadfile ~/sqllib/function/$SHLIBNAME
db2 -tv <<!EOF
CONNECT TO $1;
DROP FUNCTION READ_FILE;
CREATE FUNCTION READ_FILE
(
FILE_NAME VARCHAR(128)
)
RETURNS TABLE (c1 varchar(15))
EXTERNAL NAME '${SHLIBNAME}!readFile'
SPECIFIC READFILE
LANGUAGE C
PARAMETER STYLE DB2SQL
${FENCED}
RETURNS NULL ON NULL INPUT
NOT DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
SCRATCHPAD
FINAL CALL
DISALLOW PARALLEL;
The above is a script so make sure you set execute bit on it. i.e. chmod +x udfcat
How to compile?
On your platform, copy bldrtn from your ~/sqllib/sample/c dir to your local directory and use this makefile to compile this program.
DB2PATH=${HOME}/sqllib
BLDRTN=./bldrtn
ALIAS=sample
UID=
PWD=
COPY=cp
ERASE=rm -f
all : \
srv
srv : \
rtn
rtn : \
udfreadfile
clean :
$(ERASE) *.o
#****************************************************************************
# 2h - make cleanall
#****************************************************************************
cleanall : \
clean
$(ERASE) udfreadfile
$(ERASE) $(DB2PATH)/function/udfreadfile
udfreadfile : udfreadfile.c
$(BLDRTN) udfreadfile $(ALIAS)
./udfcat $(ALIAS) > udfcat.log
How this UDF works?
The sample code given in this UDF reads a flat file having a column of data. The size of the data is 15 character and file can have as many rows as possible.
The UDF defines a scratchpad memory area where we store the file pointer so that it can be used for each row. When this UDF is executed for each row, the different segment of code are executed in certain order.
For example:
SQLUDF_TF_FIRST is used to initialize variables or open a file pointer. This will be executed only once.
SQLUDF_TF_FETCH is used for each row execution. Remember, there is no while loop to go through the contents of the file since different code segments are used for different events.
Use SQLUDF_TF_CLOSE or SQLUDF_TF_FINAL to free up file pointer or free up memory.
FINAL CALL is used in UDF definition so that DB2 can invoke calls for SQLUDF_TF_FIRST and SQLUDF_TF_FINAL code segments. SCRATCHPAD is used to store file pointer and make sure that the size of the SCRATCHPAD is sufficient enough to hold the structure. The default size is only 100 bytes.
How to invoke the table UDF?
SELECT * FROM TABLE (db2inst1.read_file('$HOME/tableudf/datafilename.txt'))
You can use this UDF in your query to join it with other tables etc or use it in a sub-query.
Julian day in DB2
Submitted by Vikram Khatri on Thu, 2008-06-05 20:17.DB2 provides a way to calculate date from a Julian day.
For example: 2008075 should match to the 75th day of 2008 and i.e. 2008-03-15.
If you try to do,
$ db2 values date(2008075)
You will get a date of 12/03/5498 but that is what you do not want. The argument to DATE was supplied as an INT. You must supply julian day as '2008075' and it must be CHAR(7).
$ db2 values date('2008075')
and you should get '03/15/2008'.
What if, you want to calculate reverse date from a Julian day. For example, you should get '03/15/2008' from '2008075'.
Use the following SQL to get reverse from the Julian day.
$ db2 values cast(trim(char(year('2008-03-15')))||REPEAT('0',(3-LENGTH(TRIM(CHAR(DAYOFYEAR('2008-03-15'))))))||TRIM(CHAR(DAYOFYEAR('2008-03-15'))) as char(7))
and you will get '2008075'.
By the way, DB2 has a function called Julian_day but it gives the number of days from Jan 1, 4713 BC.
$ db2 values julian_day('2008-03-15') and you will get number of days 2454541 since Jan 1, 4713 BC.
--#SET TERMINATOR @
DROP FUNCTION DATE_JULIAN
@
CREATE FUNCTION DATE_JULIAN(IN_DATE DATE)
RETURNS CHAR(7)
RETURN
VALUES CAST(TRIM(CHAR(YEAR(IN_DATE)))||
REPEAT('0',(3-LENGTH(TRIM(CHAR(DAYOFYEAR(IN_DATE))))))||
TRIM(CHAR(DAYOFYEAR(IN_DATE))) AS CHAR(7))
@
values date_julian(date('03/15/2008'))@
values date_julian(date('2008-03-15'))@
Above 2 values will return 2008075.
How to create Explain Plan of DB2 Stored Procedures?
Submitted by Vikram Khatri on Thu, 2008-06-05 17:08.Step-1 : Create Explain tables
See this entry http://www.db2ude.com/?q=node/65 for how to create explain tables in DB2.
Please note: You need to create explain tables in the USER-ID of the logged-in user. If you created explain plans in the schema name of some userid, other logged-in users can not use the explain tables owned by other users. In other words, each user should have their own explain tables. This is true even when you are using SET CURRENT SCHEMA command to set schema name for the tables that you are using.
Step-2 : Set parameters so that query compiler can populate explain tables
You have 2 options here:
- Set it once at the instance level so that every procedure you create is explained
- Set it at DB2 session level so that you control which procedures need to be explained.
Personally, I like second option.
Option - 1 : Set at the instance level
$ db2set DB2_SQLROUTINE_PREPOPTS="EXPLAIN ALL EXPLSNAP ALL" $ db2stop $ db2start
Please note: Instance rebound is necessary for the DB2 registry variables to take effect.
Option - 2 : Set at the DB2 session level
In your DB2 Stored Procedure script, call SET_ROUTINE_OPTS parameter to let query compiler to create explain informations. For example, your stored procedure script may look like following:
testsp.sql
----------
--#SET TERMINATOR ;
CALL SYSPROC.SET_ROUTINE_OPTS('EXPLAIN ALL');
SET CURRENT SCHEMA = 'DB2INST1';
SET CURRENT FUNCTION PATH SYSIBM,SYSCAT,SYSFUN,DB2INST1;
DROP PROCEDURE testsp;
--#SET TERMINATOR @
CREATE PROCEDURE testsp
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR VALUES CURRENT TIMESTAMP;
OPEN c1;
END
@
Create SQL Procedure
$ db2 connect to sample $ db2 -tf testsp.sql
Step-3 : Mapping procedure name to package Name
Each DB2 SQL routine has a procedure name (which is used in CALL statements), but is represented internally by a system-generated package name. The data in the explain tables will refer to the package name, not the procedure name, hence we must know the procedure-to-package mapping so that we can give the proper package name to exfmt when dumping plans.
select deps.bschema SCHEMA,
procs.routinename PROCEDURE,
deps.bname PACKAGE,
procs.valid VALID
from sysibm.sysdependencies deps,
sysibm.sysroutines procs
where deps.dtype = 'F'
and deps.btype = 'K'
and procs.specificname = deps.dname
and procs.routineschema = deps.dschema
order by 1,2;
Step -4 : Extract Explain Plan using db2exfmt or db2expln tool.
You can call db2exfmt or db2expln tool using the package name determined in the previous step.
db2exfmt -d <dbname> -e <schema> -s <schema> -w -1 -n <package name> -g -# 0 -o <output file> db2expln -d <dbname> -c <schema> -p <package name> -s 0 -g -o <output file>
Step -5 : Putting it all together to get the explain plan in a single step.
After you have have created procedures, you need minimum 2 steps to extract the explain plan for a SQL Procedure. First - get the package name used internally by the SQL Procedure and Second - use either db2exfmt or db2expln tool to get the explain plan.
For Linux/Unix Systems
#!/bin/ksh
if [ $# -eq 0 ] ;
then
echo Usage: `basename $0` schema.procedure
exit -1
fi
schema=${1%%.*}
procedure=${1#*.}
if [ "$schema" = "" ] || [ "$schema" = "$procedure" ] ;
then
schema=$USER
procedure=$1
fi
dbname=`db2 -tx "select reg_var_value from sysibmadm.reg_variables \
where reg_var_name = 'DB2DBDFT' \
and dbpartitionnum = 0"`
if [ "$dbname" = "" ] ;
then
echo DB2 registry variable DB2DBDFT is not set.
exit -1
fi
pkgname=`db2 -tx "select VARCHAR(deps.bname,25) PACKAGE \
from sysibm.sysdependencies deps,\
sysibm.sysroutines procs\
where deps.dtype = 'F' \
and deps.btype = 'K'\
and procs.specificname = deps.dname\
and procs.routineschema = deps.dschema\
and procs.routineschema = upper('$schema')\
and procs.routinename = upper('$procedure')"`
echo Creating explain plan in database $dbname for $schema.$procedure using package $pkgname
#db2expln -d $dbname -c $schema -p $pkgname -s 0 -g -o $procedure.exp
db2exfmt -d $dbname -e $schema -s $schema -w -1 -n $pkgname -g -# 0 -o $procedure.exp
How to run in Linux/Unix
$ ./exp testsp or $ ./exp db2inst1.testsp
Note: If you do not specify schema name, logged-in user id is used as a schema name. The syntax is schema.procedure.
For Windows
You need 2 scripts to automate db2 explain plan for a SQL Procedure.
Script-1: pkg.cmd
@echo off
REM Package name for a stored procedure
SET SCHEMA=%1%
SET PROCEDURE=%2%
db2 connect to sample > NUL
db2 -x select VARCHAR(deps.bname,25) PACKAGE ^
from sysibm.sysdependencies deps,^
sysibm.sysroutines procs ^
where deps.dtype = 'F' ^
and deps.btype = 'K' ^
and procs.specificname = deps.dname ^
and procs.routineschema = deps.dschema ^
and procs.routineschema = upper('%SCHEMA%') ^
and procs.routinename = upper('%PROCEDURE%')
db2 connect reset > NUL
Script-2: exp.cmd
@echo off
cls
@ECHO Executed by: %USERNAME% Machine: %COMPUTERNAME% On %OS% %DATE%
IF "%1" == "" GOTO :Usage1
IF "%2" == "" GOTO :Usage2
SET SCHEMA=%1
SET PROCEDURE=%2
FOR /F "tokens=1" %%K in ('call pkg.cmd %SCHEMA% %PROCEDURE%') do SET PKGNAME=%%K
db2exfmt -d SAMPLE -e %USERNAME% -s %USERNAME% -w -1 -n %PKGNAME% -g -# 0 -o %PROCEDURE%.EXPLAIN
goto :end
:Usage1
ECHO You did not provide schema name for this script
ECHO.
ECHO Usage: exp schame_name procedure_name
ECHO.
goto :end
:Usage2
ECHO You did not provide procedure name for this script
ECHO.
ECHO Usage: exp schame_name procedure_name
ECHO.
:end
How to run in Windows
C:\exp schemaname procedurename
Please note: The database name was hard coded in the scripts. I did not had time to automate this. If you, please send me revised script at vikram@zinox.com.
All 3 scripts are also attached in a zip file at http://www.db2ude.com/files/explainscript.zip
What if Stored Procedures contain dynamic SQL?
If your stored procedure has dynamic SQL calls such as use of the EXECUTE command or use of the global temporary tables (GTT), you have dynamic SQL statements for which you will not get the explain plan during compile process. You need to execute the stored procedure once to get the explain plan for all dynamic SQL statements after you have completed the above step.
Follow this procedure to get the explain plan when you have used GTTs or dynamic SQL statements.
For example, you have the following stored procedure having dynamic as well as static calls.
dynsp.sql
------
CREATE PROCEDURE DYNSP
language SQL
BEGIN
DECLARE GLOBAL TEMPORARY TABLE SESSION.tt_mytemp
(
c1 INT NOT NULL,
c2 INT NOT NULL,
c3 INT NOT NULL
) NOT LOGGED WITH REPLACE ON COMMIT PRESERVE ROWS;
DECLARE GLOBAL TEMPORARY TABLE SESSION.tt_yrtemp
(
c1 INT NOT NULL,
c2 INT NOT NULL,
c3 INT NOT NULL
) NOT LOGGED WITH REPLACE ON COMMIT PRESERVE ROWS;
INSERT INTO SESSION.tt_mytemp VALUES (1,2,3),(2,3,4),(3,4,5),(4,5,6),(5,6,7);
INSERT INTO SESSION.tt_yrtemp VALUES (5,6,7),(4,5,6),(8,9,10),(9,10,11),(10,11,12);
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR SELECT t1.c1,t1.c2,t1.c3
FROM SESSION.tt_mytemp t1, SESSION.tt_yrtemp t2
WHERE t1.c1 = t2.c2;
OPEN c1;
END;
END
@
db2 connect to sample
db2 "CALL SYSPROC.SET_ROUTINE_OPTS('EXPLAIN ALL')"
db2 -tf dynsp.sql
The above step gives you explain plan for all static SQL calls in the SP. Since you have used GTT, you need to do one extra step of getting the explain plan
db2 connect to sample db2 set current explain mode yes db2 "call dynsp()" db2 set current explain mode no ./exp dynsp
Now, look at the dynsp.exp file and it will have explain plan for all dynamic SQL statements as well as static SQLs contained in your stored procedure.
Acknowledgment: Thanks to John Hornibrook and Sunil Kamath of DB2 Toronto Lab to provide help on this.
Optimistic Locking in DB2 9.5 LUW
Submitted by Werner Schuetz on Wed, 2008-04-30 12:13.Improve concurrency with DB2 9.5 optimistic locking
New optimistic locking feature to avoid maintaining long-lived locks
IBM® DB2®, Version 9.5 for Linux®, UNIX®, and Windows® provides enhanced optimistic locking support, a technique for SQL database applications that does not hold row locks between selecting and updating, or deleting rows. Gain an understanding of this enhancement, and learn how applications using this programming model benefit from this enhanced optimistic locking feature and gain improved concurrency.
Pessimistic vs. optimistic locking
Pessimistic locking
A pessimistic locking strategy assumes that the probability is high that another user will try to modify the same row in a table that you are changing. A lock is held between the time a row is selected and the time that a searched update or delete operation is attempted on that row (for example, by using the repeatable read isolation level or lock the table in exclusive mode). The advantage of pessimistic locking is that it is guaranteed that changes are made consistently and safely. The major disadvantage is that this locking strategy might not be very scalable. On a system with many users or with long-living transactions, or when transactions involve a greater number of entities, the probability of having to wait for a lock to be released increases.
Figure 1 illustrates the functioning of pessimistic locking. Transaction 1 reads a specific record and places a lock on that row. It takes some time to decide whether the row will be updated. In the meantime, transaction 2 wants access to that same row, but it has to wait until the lock is released by Transaction 1. Until then, transaction 2 will receive results from its SELECT and can continue with its business logic.
Figure 1. Pessimistic locking concept
Optimistic locking
The main problem with a pessimistic locking approach is that transactions have to wait for each other. A way to avoid this is to follow an optimistic locking strategy and assume that it is very unlikely that another user will try to change the same row that you are changing. If the row does change, the update or delete will fail, and the application logic handles such failures by, for example, retrying the select. With this approach, no locks are held between selecting and updating, or deleting a row. But, consequently, this method requires a way to ensure that the data has not changed between the time of being read and being altered. Although more retry logic in the application is needed, the primary advantage of an optimistic locking strategy is that it minimizes the time for which a given resource is unavailable for use by other transactions and thus will be a more scalable locking alternative than pessimistic locking.
Figure 2 illustrates the idea behind optimistic locking. Similar to Figure 1, transaction 1 reads a specific record but then releases its lock. Transaction 2 is now not prevented from retrieving that same row. Before committing the transaction, both transaction 1 and transaction 2 must check whether the row has changed after the previous SELECT. If a change has occurred, the transaction must restart with a new SELECT in order to retrieve the current data. However, if that row has not been changed after the previous SELECT, the data can be successfully updated.
Figure 2. Optimistic locking concept
Enhanced optimistic locking with DB2 9.5
Optimistic locking in DB2 9.5 improves scalability by minimizing the time for which a given resource is unavailable for use by other transactions. Because the database manager can determine when a row is changed, it can ensure data integrity while limiting the time that locks are held. With optimistic concurrency control, the database manager releases the row or page locks immediately after a read operation.
DB2 9.5 for Linux, Unix, and Windows adds support for easier and faster optimistic locking with no false positives. This support is added using the following new SQL functions, expressions, and features:
-
Row identifier (
RID_BITorRID) built-in function: This built-infunctioncan be used in theSELECTlist or predicates statement. In a predicate, for example,WHERE RID_BIT(tab)=?, theRID_BITequals predicate is implemented as a new direct access method in order to efficiently locate the row. Previously, thus called values optimistic locking with values was done by adding all the selected column values to the predicates and relying on someunique columncombinations to qualify only a single row, with a less efficient access method. -
ROW CHANGE TOKENexpression: This new expression returns a token as BIGINT. The token represents a relative point in the modification sequence of a row. An application can compare the current row change token value of a row with the row change token value that was stored when the row was last fetched to determine whether the row has changed. -
Time-based update detection: This feature is added to SQL using the
ROW CHANGE TIMESTAMPexpression. To support this feature, the table needs to have a new generated row change timestamp column defined to store the timestamp values. This can be added to existing tables using theALTER TABLEstatement, or the row change timestamp column can defined when creating a new table. The row change timestamp column's existence also affects the behavior of optimistic locking in that the column is used to improve the granularity of the row change token from page level to row level, which could greatly benefit optimistic locking applications. -
Implicitly hidden columns: For compatibility, this feature eases the adoption of the row change timestamp columns to existing tables and applications. Implicitly hidden columns are not externalized when implicit column lists are used. For example a
SELECT* against the table does not return a implicitly hidden columns in the result table and anINSERTstatement without a column list does not expect a value for implicitly hidden columns, but the column should be defined to allow nulls or have another default value.
Applications using this programming model will benefit from the enhanced optimistic locking feature. Note that applications that do not use this programming model are not considered optimistic locking applications, and they will continue to work as before.
Figure 3 illustrates the functioning of DB2 9.5 optimistic locking. Both transaction 1 and transaction 2 read the same row, including the RID_BIT and the ROW CHANGE TOKEN value. Then transaction 1 updates the row after ensuring that the row has not changed after the previous SELECT by adding a RID_BIT and ROW CHANGE TOKEN predicate to the UPDATE statement. When transaction 2 now tries to update that same row using the same predicate as transaction 1, the row will not be found because the value of the ROW CHANGE TOKEN has changed regarding to the UPDATE of transaction 1. Transaction 2 has to start a retry in order to retrieve the current data.
Figure 3. Enhanced optimistic locking with DB2 9.5
Enabling optimistic locking
Since the new SQL expressions and attributes for optimistic locking can be used with no DDL changes to the tables involved, you can easily try optimistic locking in your test applications.
Note that without DDL changes, optimistic locking applications may get more false negatives than with DDL changes. An application that does get false negatives may not scale well in a production environment because the false negatives may cause too many retries. Therefore, to avoid false negatives, optimistic locking target tables should be either be created with a ROW CHANGE TIMESTAMP column or altered to contain the ROW CHANGE TIMESTAMP column.
CREATE TABLE EMPLOYEE (EMPNO CHAR(6) NOT NULL,
......
ROWCHGTS TIMESTAMP NOT NULL
GENERATED ALWAYS
FOR EACH ROW ON UPDATE AS
ROW CHANGE TIMESTAMP)
ALTER TABLE EMPLOYEE ADD COLUMN
ROWCHGTS TIMESTAMP NOT NULL
GENERATED ALWAYS
FOR EACH ROW ON UPDATE AS
ROW CHANGE TIMESTAMP
|
These are a basic steps to be performed in order to enable optimistic locking support in your applications:
- In the initial query,
SELECTthe row identifier using theRID_BIT()andRID()built-in function) and row change token for each row that you need to process. - Release the row locks so that other applications can
SELECT,INSERT,UPDATE, andDELETEfrom the table (for example, use isolation level cursor stability or uncommitted read). - Perform a searched
UPDATEorDELETEon the target rows, using the row identifier and row change token in the search condition, optimistically assuming that the unlocked row has not changed since the originalSELECTstatement. - If the row has changed, the
UPDATEoperation will fail and the application logic must handle the failure. For instance, the application retries theSELECTandUPDATEoperations.
Usage scenario
An employee got a new job responsibility and is now working for another department. Two managers (the manager of the old department called Manager1 and Manager2 from the new department) are using a personnel administration application to update employee records in the EMPLOYEE table in the SAMPLE database. There is a possibility that both managers try to update the same employee record at the same time.
The EMPLOYEE table contains a implicitly hidden ROW CHANGE TIMESTAMP column and is accessed by Manager1 and Manager2 simultaneously. Manager1 selects the data from the EMPLOYEE table and later tries to update the same data. However, between his select and his update, Manager2 updates the same data. Manager2's update is successful, but Manager1's update fails.
Result from the SELECT (Manager1 and Manager2)
| RID_BIT | ROW CHANGE TOKEN | EMPNO | FIRSTNME | LASTNAME | PHONENO | ROW CHANGE TIMESTAMP | x'0400400100000000 0000000000FA9023' |
74904229642240 | 000010 | CHRISTINE | HAAS | 3978 | 0001-01-01 00:00:00.000000 |
|---|---|---|---|---|---|---|
| x'0500400100000000 0000000000FA9023' |
74904229642240 | 000020 | MICHAEL | THOMPSON | 3476 | 0001-01-01 00:00:00.000000 |
| x'0600400100000000 0000000000FA9023' |
74904229642240 | 000030 | SALLY | KWAN | 4738 | 0001-01-01 00:00:00.000000 |
UPDATE statement (Manager2)
UPDATE EMPLOYEE SET
(FIRSTNME,LASTNAME,PHONENO) = ('CHRISTINE','HAAS','1092')
WHERE RID_BIT(EMPLOYEE)=x'04004001000000000000000000FA9023' AND
ROW CHANGE TOKEN FOR EMPLOYEE=74904229642240
|
Result from the UPDATE (Manager2)
| RID_BIT | ROW CHANGE TOKEN | EMPNO | FIRSTNME | LASTNAME | PHONENO | ROW CHANGE TIMESTAMP |
|---|---|---|---|---|---|---|
| x'0400400100000000 0000000000FA9023' |
141285645885181032 | 000010 | CHRISTINE | HAAS | 1092 | 2007-12-2011:55:45.593000 |
| x'0500400100000000 0000000000FA9023' |
74904229642240 | 000020 | MICHAEL | THOMPSON | 3476 | 0001-01-01 00:00:00.000000 |
| x'0600400100000000 0000000000FA9023' |
74904229642240 | 000030 | SALLY | KWAN | 4738 | 0001-01-01 00:00:00.000000 |
UPDATE statement (Manager1)
UPDATE EMPLOYEE SET
(FIRSTNME,LASTNAME,PHONENO) = ('CHRISTINE','HAAS','1092')
WHERE RID_BIT(EMPLOYEE)=x'04004001000000000000000000FA9023' AND
ROW CHANGE TOKEN FOR EMPLOYEE=74904229642240
|
Result from the UPDATE (Manager1)
The update from Manager1 is unsuccessful. Since the ROW CHANGE TOKEN has changed by the UPDATE of Manager2, the ROW CHANGE TOKEN predicate of Manager1's UPDATE statement fails while comparing the token retrieved at the point of the SELECT and the current value after having been updated by Manager2's application. Thus the UPDATE fails to find the specified row. A message "SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000" is returned.
More detailed information and usage scenarios
More details can and usage scenarios be found in an IBM DeveloperWorks article "Improve concurrency with DB2 9.5 optimistic locking" at http://www.ibm.com/developerworks/db2/library/techarticle/dm-0801schuetz
How to setup the Self Tuning Memory Manager (STMM)
Submitted by Werner Schuetz on Wed, 2008-04-30 11:38.Self-tuning memory was first introduced in IBM ® DB2 ® 9.1 and simplifies the task of memory configuration by automatically setting optimal values for most memory configuration parameters, including buffer pools, package cache, locking memory, sort heap, and total database shared memory. When the self tuning memory manager (STMM) is enabled, the memory tuner dynamically distributes the available memory among the various memory consumers.
STMM modes of operation
STMM works in four different modes
- Mode 1: DATABASE_MEMORY= AUTOMATIC
In this case, the required memory is taken from and returned to the OS when required. The total amount of memory used by DB2 can grow over time and is limited only by the operating system's memory availability. -
Mode 2: DATABASE_MEMORY= <NUMERIC VALUE> AUTOMATIC
In this case, memory tuning still occurs, but the total memory used by the database would start from the NUMERIC value and can grow over time. It is only limited by the operating system's available memory. -
Mode 3: DATABASE_MEMORY=<NUMERIC VALUE>
DB2 will allocate this amount of memory during startup and this memory setting is static. It can not take or give memory to the operating system on demand. -
Mode 4: DATABASE_MEMORY=COMPUTED
The database memory is computed based on the sum of initial values of the database memory heaps during the database startup. In this case DATABASE_MEMORY is not enabled for self tuning.
Activating self-tuning memory
UPDATE DATABASE CONFIGURATION USING DATABASE_MEMORY AUTOMATIC
UPDATE DATABASE CONFIGURATION USING SELF_TUNING_MEM ON IMMEDIATE
UPDATE DATABASE CONFIGURATION USING SORTHEAP AUTOMATIC
UPDATE DATABASE CONFIGURATION USING SHEAPTHRES_SHR AUTOMATIC
UPDATE DATABASE CONFIGURATION USING LOCKLIST AUTOMATIC
UPDATE DATABASE CONFIGURATION USING MAXLOCKS AUTOMATIC
UPDATE DATABASE CONFIGURATION USING PCKCACHESZ 8196 AUTOMATIC IMMEDIATE
ALTER BUFFERPOOL BPname1 IMMEDIATE SIZE 1000 AUTOMATIC
ALTER BUFFERPOOL BPname2 IMMEDIATE SIZE 1000 AUTOMATIC
Determining which memory consumers are enabled for self tuning
GET DB CFG SHOW DETAIL Description Parameter Current Value Delayed Value ----------------------------------------------------------------------------------------------- Self tuning memory (SELF_TUNING_MEM) = ON (Active) ON Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(37200) AUTOMATIC(37200) Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(7456) AUTOMATIC(7456) Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(98) AUTOMATIC(98) Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(8196) AUTOMATIC(5600) Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(5000) AUTOMATIC(5000) Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(256) AUTOMATIC(256)
Verifying which bufferpools are enabled for self tuning
SELECT BPNAME, NPAGES, PAGESIZE FROM SYSCAT.BUFFERPOOLS BPNAM NPAGES PAGESIZE ----------------------------------------------- IBMDEFAULTBP -2 4096 BPname1 -2 8192 BPname2 -2 32768
A bufferpool is enabled for self tuning, when NPAGES has a value of -2
Controlling DB2 Memory consumption for an Instance
The instance_memory configuration parameter specifies the maximum amount of memory that can be allocated for a database partition.
When instance_memory is set to AUTOMATIC, a fixed upper bound on total memory consumption for the instance is set at instance startup (db2start). Actual memory consumption by DB2 varies depending on the workload. When STMM is enabled to perform database_memory tuning (by default for new databases), during run-time, STMM dynamically updates the size of performance-critical heaps within the database shared memory set
according to the free physical memory on the system, while ensuring that there is sufficient free instance_memory available for functional memory requirements.
- For heavily-used instances, STMM increases the size of performance-critical heaps as needed. More functional memory is consumed, as there are more database agents servicing applications and consuming functional memory. If there is enough free instance_memory but very little free physical memory on the system, STMM starts decreasing the size of performance-critical heaps ensure that the system does not start paging. As functional memory requirements drop, free physical memory on the system should increase, and STMM will start increasing the performance-critical heaps again.
- For less heavily-used instances, there is less functional memory consumed by the instance, and if there is insufficient free physical memory left on the system, STMM shrinks performance-critical heaps.
If instance_memory is set to a specific value, and at least one active database has an AUTOMATIC value for database_memory, and STMM is enabled for that database, then STMM increases the database_memory size such that DB2 uses almost the entire amount of memory specified by instance_memory, ensuring only that enough free instance_memory is available
for functional memory requests. In this scenario, STMM does not monitor free physical memory on the machine, therefore, instance_memory must be configured properly to ensure that paging will not occur.
Disabling self tuning memory
Self tuning can be disabled for the entire database by setting self_tuning_mem to OFF. When self_tuning_mem is set to OFF, the memory configuration parameters and buffer pools that are set to AUTOMATIC remain AUTOMATIC and the memory areas remain at their current size.
UPDATE DATABASE CONFIGURATION USING SELF_TUNING_MEM OFF IMMEDIATE
Self tuning can also be effectively deactivated for the entire database if only a single memory consumer is enabled for self tuning. This is because memory cannot be redistributed when only one memory area is enabled. For example, to disable self tuning of the sortheap configuration parameter, you could enter the following:
UPDATE DATABASE CONFIGURATION USING SORTHEAP MANUAL
To disable self tuning of the sortheap configuration parameter and change the current value of sortheap to 2000 at the same time, enter the following:
UPDATE DATABASE CONFIGURATION USING SORTHEAP 2000
More detailed information
More details can be found on these very helpful article:
"DB2 9 self-tuning memory management" at
http://www.ibm.com/developerworks/edu/dm-dw-dm-0611read2-i.html
"Understanding the advantages of DB2 9 autonomic computing features" at
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0709saraswatipura/
"DB2 self-tuning memory manager log parser" at
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0708naqvi/
DB2 LUW Deep Compression
Submitted by Mark Mulligan on Tue, 2008-04-01 14:40.Introduction
This article discusses DB2 LUW Deep Compression from the perspective of someone who planned, organized, communicated and coordinated the implementation of this in development, test, regression test and production environments for multi-terabyte data warehouse databases.
Pros and cons
DB2 LUW Deep Compression reduces the amount of disk space needed to store information in databases and improves performance through faster I/O and more efficient use of memory. Here is a list of our production databases showing the size before and after compression along with the compression ratios and space savings we accrued.
We measured performance improvements in the 5% to 30% range. We determined that more rows were stored in buffer pools and that application memory high water marks were lower. We reduced our application memory configuration and increased our buffer pool memory configuration to better utilize the memory we had available on servers. Overall performance of online and batch SQL has improved significantly and better memory management by DB2 has helped us avoid having to purchase more memory to handle increased work loads. The combined improvement in I/O and memory utilization of DB2 LUW Deep Compression has made this a good investment for our large databases.
DB2 LUW Deep Compression uses a bit more CPU, requires more of a conversion effort if system managed tablespaces (SMS) are being used and costs more to license than DB2 LUW without compression. We measured CPU utilization increases of 2-5% after implementing compression and suspect this cost is due to the decompression that is done before information is returned to applications. In our environments the improved I/O and memory management performance far outweigh this small CPU cost.
Environment
AIX 5.3.0.0 Technology Level 6
DB2 V9.1 FIX PAK 2
DB2 Database Partitioning: "Licensed"
DB2 Storage Optimization: "Licensed"
Detail Steps
- Alter database managed tablespace (DMS) from REGULAR to LARGE.
- Alter DMS tablespace to AUTORESIZE YES.
- Alter tables in DMS tablespace to COMPRESS YES and VALUE COMPRESSION.
- Run reorg using the temporary tablespace and RESETDICTIONARY options.
- Run DB2 list tablespaces show detail command.
- Recycle the DB2 instance.
- Run reorg in place to free up space inside DMS tablespaces.
- Run DB2 list tablespaces show detail command.
- Recycle the DB2 instance.
- Alter tablespaces to reduce the size of containers to recover disk space.
- Use the copy/rename table approach for large tables in DMS tablespaces.
- Create new DMS LARGE tablespaces to replace SMS tablespaces.
- Use the copy/rename table approach for tables in SMS tablespaces.
Recommendations and Information
- Start with the smallest DMS tablespace first and work toward the largest.
- Start with the smallest table in a tablespace first and work toward the largest table in that tablespace. DB2 will not implement compression for a table if it does not have enough rows to warrant this. We found it faster and easier to alter all tables to be compressed and to simply let DB2 decide whether to build the compression dictionary and compress the data during the reorg.
- Using the smallest to largest approach helps in several ways. First, experience is gained while working with smaller units of work. Second, if you have multiple tables in a tablespace, DB2 will recover more space for reuse after compression is implemented due to the way DB2 stores data internally in tablespaces. Third, using the smallest table to the largest table will allow you to free up disk space as you work through the project. This space can be reused to support the additional space you may need when using the copy/rename table approach for large tables or for tables you are converting from SMS to DMS tablespaces.
- Complete the alter tablespace to LARGE, alter table and reorgs with the temporary tablespace and resetdictionary options for all tables in a tablespace before altering the next tablespace to LARGE.
- Plan tablespace alters, table alters and reorgs during times when tables are not in great demand. We found that we could run alters and reorgs for small to medium size tables (less than a billion rows) during the day when we have a lot of SQL selects running against our data warehouses. We did not run alters or reorgs during our nightly batch and replication cycle windows. We had a few -911 time out roll back errors on alters and reorgs and simply had to rerun these. We had a few times when people using our data warehouses got a -911 time out roll back error. We avoided most of this by using the copy/rename table approach for large tables (more than a billion rows).
- The alter DMS tablespace AUTORESIZE YES option makes DMS tablespaces behave like SMS tablespaces because they will automatically increase in size when more space is needed.
- The reorg with the resetdictionary option builds the compression dictionary and compresses the data in the table if DB2 thinks there is enough data to make this worthwhile. Pages of data are converted from regular record identifiers (RIDS) to large RIDS whether compressed or not. Large RIDS result in more data being stored per page and this combined with compression reduces the amount of disk space needed to store the information and it reduces the number of I/O’s required to return the information.
- The db2 list tablespaces show detail command resets some internal pointers in tablespaces after they have been converted to large RIDS with compressed tables. This is required in order to be able to recover space for reuse.
- Recycling the DB2 instance flushes the bufferpools and DB2 will use these more efficiently with compressed data when the DB2 instance is started. Tablespace information in memory is also refreshed after the instance is recycled.
- The second table reorg will lower the high watermark in the table and tablespace and allow you to reduce tablespace containers to recover space for reuse. Be sure to reorg the smallest tables first and work your way toward the largest. This approach will recover the most space due to the way information is stored internally in tablespaces that contain many tables.
- Alter the DMS tablespace to reduce the size of container files to recover disk space for reuse when you have completed implementing compression for all tables in the tablespace. We found that doing this with a script that loops and attempts to reduce container sizes using smaller values works best. Containers are reduced in size until there is no longer free space to allow this and then the script will get warning messages as it works it’s way down in size. DB2 version 9.5 will automatically reduce the size of DMS tablespaces defined with the AUTORESIZE YES feature. You have to run DB2 alter commands to reduce tablespace container sizes for tablespaces with volatile information in releases before DB2 v9.5.
- Space recovered after compressing small and medium tables can be used to increase file systems that have temporary tablespace container files. This can ensure enough temporary work space is available to rebuild multi-billion row indexes of the largest tables in a database.
- Additional space recovered after compressing small and medium tables can be used to support the copy/rename approach on large tables and on tables converted from SMS to DMS tablespaces.
- The copy/rename approach simply means to create a new DMS LARGE tablespace, a new table and new indexes, to copy the data from the current table to the new table and to implement compression on the new table. Then the current table can be renamed to an old name and the new table renamed to the current name. Since the rename takes seconds, this avoids long outages when you need to implement compression against large tables. The old table and tablespace can be dropped.
- Reorgs should usually be run serially and not in parallel against multiple tables at the same time if you want to reduce the impact the compression project has on other users of the database.
- Recycling instances can be coordinated based on production service level agreements.
Summary
It took us about a week to implement compression in each of our development and test environments. It took about two weeks to implement compression in each of our user regression test environments. It took from four to six weeks to implement compression in each of our production environments. We only ran alters and reorgs during the day and not at night during our nightly batch and replication cycles. Coordinating instance outages increased the elapsed time.
The amount of space we recovered for reuse and the performance improvement we have seen through reduced I/O’s and better database memory utilization make DB2 LUW Deep Compression well worth the investment of time and money.
Script Examples
The script examples below can help provide information that can be used to plan and manage projects to implement DB2 LUW Deep Compression. DBA’s can combine and enhance these scripts to generate the commands needed to alter tablespaces, alter tables and reorganize tables in order to convert regular record identifiers to large record identifiers in tablespaces and to build compression dictionaries and compress data in tables. Ordering the generated command output so that tablespaces and tables are converted from smallest to largest will help you recover more space for reuse during a compression project. Small and medium tablepsaces and tables can be converted using the generated commands and large tables can be skipped in this process and converted using the copy/rename table approach mentioned earlier in this article.
(Replace the word Database in the script with your database name.)
db2list_candidate_tablespaces_for_conversion.ksh
# Description : This script lists tablespaces that are candidates for # : conversion from SMS to DMS and from DMS regular to # : DMS large in support of a project to implement large # : record identifiers so that DB2 will store more rows # : per page. This effort could be part of a project # : to implement compression because table compression # : requires large record identifiers. (RIDS) db2 connect to Database echo "Listing candidate tablespaces for conversion." db2 -x "select TBSP_TYPE,sum(TBSP_USED_SIZE_KB) as TBSP_USED_SIZE_KB, char(TBSP_NAME,20) as TBSP_NAME,TBSP_CONTENT_TYPE,TBSP_PAGE_SIZE,TBSP_AUTO_RESIZE_ENABLED from sysibmadm.tbsp_utilization where TBSP_CONTENT_TYPE not like '%TEMP%' group by TBSP_TYPE,TBSP_NAME,TBSP_CONTENT_TYPE, TBSP_PAGE_SIZE,TBSP_AUTO_RESIZE_ENABLED" > FileName.dat sort FileName.dat > FileName.txt cat FileName.txt rm FileName.dat echo "Candidate tablespaces for conversion located in FileName.txt"
db2list_candidate_tables_for_compression.ksh
# Description : This script lists candidate tables for compression.
# : A text file is generated with information about the
# : server, instance, database, # tablespaces, tablespace
# : types (D=DMS, S=SMS), schema, table name and row
# : count (cardinality) from DB2 SYSCAT catalog views.
# : Output can be imported into a spreadsheet
# : and used to help plan a compression project.
#
db2 connect to Database
echo "Identifying tables that are candidates for compression."
db2 -x "select a.tbspace,b.tbspacetype,digits(a.card),a.tabschema,a.tabname
from syscat.tables a,syscat.tablespaces b
where a.tabschema not like 'SYS%'
and a.tbspace=b.tbspace and a.card > 0
and a.type in ('T','S')
order by a.card desc,a.tbspace,a.tabschema,a.tabname" > FileName.dat
cat FileName.dat | while read Tbspace TbspaceType Card TabSchema TabName
do
echo '"'$(hostname)'","'${DB2INSTANCE}'","'Database'","'${Tbspace}'","
'${TbspaceType}'","'${TabSchema}'","'${TabName}'","'${Card}'"' | tee -a FileName_excel.txt
done
rm FileName.dat
echo "Candidate tables for compression located in FileName_excel.txt"
Space savings
| DB2 LUW | Terabytes | Terabytes | Percent | Partitions |
| Database | Before | After | compressed | |
| prod001 | 20 | 9 | 55% | 16 |
| prod002 | 8 | 4 | 50% | 11 |
| prod003 | 12 | 6 | 50% | 11 |
| prod004 | 22 | 10 | 55% | 7 |
| prod005 | 18 | 7 | 61% | 16 |
| Total | 80 | 36 | 55% | |
| Space savings | 44 Terabytes | |||
How to setup PHP thin client on your application server?
Submitted by Vikram Khatri on Fri, 2008-03-28 21:34.You have installed DB2 on your database server and now you need to setup your IBM PHP client to connect to DB2. Do you require a full ~500 MB client install? Mostly, system administrator or DBAs grind their teeth when they find out that for a simple connect from PHP, they need to install a big fat client. But, things have changed and you can install a thin ODBC-CLI client instead of installing a fat client.
Pre-requisites
- IBM_DB2 PHP driver
- You have already compiled the driver either on a DB2 server or on a machine having thick client. Refer to this article for how to compile PHP driver
- or, you already got ibm_db2 PHP driver from somewhere for your version of PHP
- or, you plan to use ibm_db2 PHP driver available with thin ODBC-CLI driver.
[vikram@gopal php64]$ pwd /home/vikram/clidriver/php64 [vikram@gopal php64]$ ls -l total 492 -rwxr-xr-x 1 vikram vikram 174098 Oct 2 03:41 ibm_db2_4.4.6.so -rwxr-xr-x 1 vikram vikram 179801 Oct 2 03:41 ibm_db2_5.2.1.so -rwxr-xr-x 1 vikram vikram 132765 Oct 2 03:42 pdo_ibm_5.2.1.so
- or, if you like to build a RPM for distribution for ibm_db2 PHP driver, refer to http://www.db2ude.com/?q=node/26
- or, refer to the latest IBM DB2 documentation for step by step process.
Steps to get IBM thin ODBC-CLI Driver
- Go to IBM DB2 Application Web Site at http://ibm.com/software/data/db2/ad
- Click on download now link
- Click on IBM Data Server Driver for ODBC and CLI link
- After you click above link, you will be asked to login. If you do not have an IBM User ID, create one and save that for your future use as you will require that user ID for any download etc. Choose the right driver type and download it. You will see a download page similar to shown below:
- After accepting license agreement, download the driver (approx 14MB) and you will see a screen something similar to one shown below:
- After you download the ODBC-CLI driver, you need to install it manually since there is no GUI install program. Installation is nothing but uncompressing the file to a directory somewhere on your application server.
[vikram@gopal clidriver]$ pwd /home/vikram/clidriver [vikram@gopal clidriver]$ ls -l total 56 drwxr-xr-x 2 vikram vikram 4096 Oct 4 00:12 adm drwxr-xr-x 2 vikram vikram 4096 Oct 4 00:12 bin drwxr-xr-x 2 vikram vikram 4096 Oct 4 00:12 bnd drwxr-xr-x 2 vikram vikram 4096 Oct 4 00:12 cfg drwxr-xr-x 4 vikram vikram 4096 Oct 4 00:12 conv drwxr-xr-x 2 vikram vikram 4096 Oct 4 00:12 db2dump drwxr-xr-x 3 vikram vikram 4096 Mar 28 22:31 lib drwxr-xr-x 3 vikram vikram 4096 Oct 4 00:12 license drwxr-xr-x 3 vikram vikram 4096 Oct 4 00:12 msg drwxr-xr-x 2 vikram vikram 4096 Oct 2 11:51 php32 drwxr-xr-x 2 vikram vikram 4096 Oct 4 00:12 php64 drwxr-xr-x 2 vikram vikram 4096 Oct 2 11:51 ruby32 drwxr-xr-x 2 vikram vikram 4096 Oct 4 00:12 ruby64 drwxr-xr-x 3 vikram vikram 4096 Oct 4 00:12 security64
- After you have extracted files, configure it as per this link. You might not need to do any thing but this link is here for reference purpose.
- After you have copied or installed your PHP driver, make sure that you made correct entries in your php.ini file. You can check modules directory of php to make sure that you have ibm_db2.so shared library. The modules or ext or extension directory location will be different based upon your distribution.
/usr/lib64/php/modules [vikram@gopal modules]$ ls -l total 2896 -rwxr-xr-x 1 root root 337448 May 9 2007 gd.so -rwxr-xr-x 1 root root 167620 Mar 28 23:17 ibm_db2.so -rwxr-xr-x 1 root root 46152 May 9 2007 ldap.so -rwxr-xr-x 1 root root 1863856 May 9 2007 mbstring.so -rwxr-xr-x 1 root root 118952 May 9 2007 mysqli.so -rwxr-xr-x 1 root root 54088 May 9 2007 mysql.so -rwxr-xr-x 1 root root 28296 May 9 2007 pdo_mysql.so -rwxr-xr-x 1 root root 92616 May 9 2007 pdo.so -rwxr-xr-x 1 root root 24520 May 9 2007 pdo_sqlite.so -rwxr-xr-x 1 root root 15416 Nov 6 2006 phpcups.so
Now, you should see the dependencies involved for successful execution of your ibm_db2.so shared library.
[vikram@gopal modules]$ ldd ibm_db2.so libdb2.so.1 => /home/db2/sqllib/lib64/libdb2.so.1 (0x00002aaaaabbe000) libc.so.6 => /lib64/libc.so.6 (0x00002aaaacbda000) libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00002aaaace23000) libdl.so.2 => /lib64/libdl.so.2 (0x00002aaaacf57000) libpthread.so.0 => /lib64/libpthread.so.0 (0x00002aaaad05c000) libm.so.6 => /lib64/libm.so.6 (0x00002aaaad174000) libdb2dascmn.so.1 => /opt/ibm/db2/V9.5/lib64/libdb2dascmn.so.1 (0x00002aaaad2f5000) libdb2g11n.so.1 => /opt/ibm/db2/V9.5/lib64/libdb2g11n.so.1 (0x00002aaaad522000) libdb2genreg.so.1 => /opt/ibm/db2/V9.5/lib64/libdb2genreg.so.1 (0x00002aaaadda6000) libdb2install.so.1 => /opt/ibm/db2/V9.5/lib64/libdb2install.so.1 (0x00002aaaadfeb000) libdb2locale.so.1 => /opt/ibm/db2/V9.5/lib64/libdb2locale.so.1 (0x00002aaaae1f6000) libdb2osse.so.1 => /opt/ibm/db2/V9.5/lib64/libdb2osse.so.1 (0x00002aaaae419000) libdb2osse_db2.so.1 => /opt/ibm/db2/V9.5/lib64/libdb2osse_db2.so.1 (0x00002aaaaea2d000) libdb2trcapi.so.1 => /opt/ibm/db2/V9.5/lib64/libdb2trcapi.so.1 (0x00002aaaaec73000) libstdc++.so.5 => /usr/lib64/libstdc++.so.5 (0x00002aaaaee87000) libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00002aaaaf062000) /lib64/ld-linux-x86-64.so.2 (0x0000555555554000) librt.so.1 => /lib64/librt.so.1 (0x00002aaaaf170000)In above listing, you see the location of all libdb* shared libraries pointing to the location of db2 installed on my system. On a machine, where there is no DB2 client, the location of libdb2* points to unknown. After you uncompressed ODBC-CLI driver, you will need to update LD_LIBRARY_PATH in your profile to the location of lib directory of clidriver.
export LD_LIBRARY_PATH=/home/vikram/clidriver/lib
Make changes to your php.ini file for entires as shown below:
ibm_db2.instance_name=db2inst1 or your DB2 instance name [PHP_IBM_DB2] extension=php_ibm_db2.dll or ibm_db2.so or any other name that you chose to compile the driver.
Make sure that you have made above changes in correct php.ini file. For example, the following image shows the location of my php.ini file and I should be making changes to this php.ini file. I write this because my system had many php.ini and I was making changes to the wrong php.ini and struggling to find why phpinfo() is not showing those changes.
-
If PHP is able to recognize ibm_db2 driver, you should be able to verify it by running phpinfo().
Make a simple PHP page with phpinfo() and assuming that you have already set apache web server and run it.phpinfp.php file ---------------- <? phpinfo(); ?>
-
Copy following PHP script in a file and test connection to DB2 by running it either through command line or through your web page.
db2conn.php ----------- <? $database = 'SAMPLE'; $user = 'db2admin'; $password = 'password'; $hostname = 'localhost'; $port = 50000; $conn_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$database;" . "HOSTNAME=$hostname;PORT=$port;PROTOCOL=TCPIP;UID=$user;PWD=$password;"; $conn_resource = db2_connect($conn_string, '', ''); if ($conn_resource) { echo 'Connection to database succeeded.'; db2_close($conn_resource); } else { echo 'Connection to database failed.'; echo 'SQLSTATE value: ' . db2_conn_error(); echo 'with Message: ' . db2_conn_errormsg(); } ?> $ php db2conn.php Connection to database succeeded.If you notice above, we are making a direct connection to the DB2 database using server name, port number and by using the name of the database at the server. If we had a full DB2 client, we would have cataloged DB2 databases locally but we could not do that here since thin client does not come with DB2 CLP.
If you in fact had DB2 CLP (or fat client), you could have used following script that uses the database directory to resolve the local alias name of DB2. Please note that the following DB2 connection script will not work with thin ODBC-CLI driver as we are only specifying name of the database and no host name or port number. This is due to the fact that we catalog a remote DB2 database locally through a regular DB2 client and PHP driver will resolve host name or port number through DB2 database directory but that is not the case with the thin ODBC-CLI client. However, it is a best practice to use DB2 connection as shown in the above script since it will work for both thin and thick db2 clients. This may sound simple for the people who know about DB2 but I find people struggling on this topic very much.<?php $db_name = 'SAMPLE'; $usr_name = 'db2inst1'; $password = 'password'; // For persistent connection change db2_connect to db2_pconnect $conn_resource = db2_connect($db_name, $usr_name, $password); if ($conn_resource) { echo 'Connection to database succeeded.'; db2_close($conn_resource); } else { echo 'Connection to database failed.'; echo 'SQLSTATE value: ' . db2_conn_error(); echo 'with Message: ' . db2_conn_errormsg(); } ?> -
If you do not succeed in connecting to DB2, make sure that you have set following on your DB2 server.
$ db2 get dbm cfg | grep -i svce TCP/IP Service name (SVCENAME) =
If SVCENAME is not set, update this parameter to a TCPIP port number
$ db2 update dbm cfg using svcename 50000 $ db2set -all
If db2set -all does not show any variable as DB2COMM, set this to TCPIP
$ db2set DB2COMM=TCPIP $ db2stop force $ db2start
After making above changes, try again.
Case insensitive string comparison in DB2
Submitted by Vikram Khatri on Thu, 2008-03-27 19:34.You are trying to figure out the best approach to do the case in-sensitive search in DB2. You must first read these 3 excellent articles written on this topic by 3 IBMers.
In a nutshell, you can take one of the approach as outlined below:
- Create a generated column in DB2 using LOWER or UPPER function
CREATE TABLE testtable (name VARCHAR(60), name_lower GENERATED ALWAYS AS (LOWER(name))) CREATE INDEX ix1_name ON testtable (name_lower) SELECT name FROM testtable WHERE LOWER(name) = 'babbu';
DB2 optimizer will actually use index ix1_name when you use LOWER function on name in your query. Please do not consider this to be an all out alternative of Oracle functional index in DB2. DB2 is smart enough for most of the functions where a functional index is not required and I will explain that in detail in some other article with actual examples. The Oracle DBAs get blind sided on this to create generated columns for an alternative of Oracle's functional indexes.
- Use index extensions
Please read Knut's article on how to use index extension capability. This approach requires using Kunt's user defined functions to use index extension capability with the use of DISTINCT TYPE data type. The use of GENERATED COLUMN requires additional storage in table but index extension approach takes that storage in index rather than in the actual table. This will be very useful when adding a GENERATED COLUMN is not a possibility due to page size. - Use COLLATION_KEY_BIT function
Please refer to Doug's article on a user defined function and what he described was implemented in DB2 and the DB2 function name is COLLATION_KEY_BIT.
SELECT name FROM testtable WHERE collation_key_bit(name,'UCA400R1_S1') = COLLATION_KEY_BIT('babbu', 'UCA400R1_S1');
If there is an index on a generated column collation_key_bit(name,'UCA400R1_S1'), DB2 will use the index as shown above otherwise it will use full table scan as shown below. You also get same behavior with the use of LOWER or UPPER function but using COLLATION_KEY_BIT function gives you much greater flexibility in comparing the strings particularly if you want to eliminate accent characters in comparison or to do culturally correct comparisons. For example, Nuernberg should match with Nürnberg. You will not get that with lower or upper function.
You can create a case insensitive database similar to the lines of collation_key_bit function to compare strings properly but let database do that comparison instead of using COLLATION_KEY_BIT function.
$ db2 CREATE DB SAMPLE COLLATE USING UCA500R1_S2 $ db2 create database mydb2 collate using UCA500R1_E0_S1 This is case sensitive but accent insensitive and will collate "role" = "rôle" < "Role" $ db2 create database mydb2 collate using UCA500R1_S1 This is both case and accent insensitive and will collate "role" = "Role" = "rôle"
If you are on DB2 9.5 or later, you can use this collation for case in-sensitive search.
create database mydb2C automatic storage yes on /db2fs USING CODESET UTF-8 TERRITORY US COLLATE USING UCA500R1_LEN_S1_NX pagesize 16384 autoconfigure apply none ;
You will notice some performance impact due to above since SYSTEM or IDENTITY collation gives the best performance.
Which approach you should take - It really depends upon pros and cons of different approaches as outlined above. If you can define all of your STRING data in all tables with a common DISTINCT TYPE and performance is the criteria and you can not use generated columns, go with the index extension approach as per Knut's UDFs. For simplicity, go with case in-sensitive database but be prepared to sacrifice some performance due to complex UCA algorithm of string compare.
The choice is yours.
How to compile DB2 PHP driver
Submitted by Vikram Khatri on Thu, 2008-03-27 17:45.# rpm -qa | grep php php-mbstring-5.1.6-1.2 php-5.1.6-1.2 php-mysql-5.1.6-1.6 php-pdo-5.1.6-1.2 php-pdo-5.1.6-1.6 php-devel-5.1.6-1.6 php-pear-1.4.9-1.2 php-5.1.6-1.6 php-mbstring-5.1.6-1.6 php-gd-5.1.6-1.6 php-mysql-5.1.6-1.2 php-devel-5.1.6-1.2 php-ldap-5.1.6-1.6
-rw-r--r-- 1 vikram vikram 4687 Aug 31 2005 ibm_db2.dsp -rwxr-xr-x 1 vikram vikram 508 Jul 11 2006 config.w32 -rw-r--r-- 1 vikram vikram 7993 Nov 20 19:06 php_ibm_db2.h -rw-r--r-- 1 vikram vikram 256 Nov 20 19:06 TODO -rw-r--r-- 1 vikram vikram 3297 Nov 20 19:14 config.m4 -rw-r--r-- 1 vikram vikram 182086 Nov 21 11:56 ibm_db2.c drwxrwxr-x 2 vikram vikram 4096 Mar 29 12:47 tests
[vikram@gopal ibm_db2-1.6.5]$ phpize Configuring for: PHP Api Version: 20041225 Zend Module Api No: 20050922 Zend Extension Api No: 220051025 [vikram@gopal ibm_db2-1.6.5]$ ls -l total 1596 -rw-r--r-- 1 vikram vikram 66540 Mar 29 12:49 acinclude.m4 -rw-rw-r-- 1 vikram vikram 297593 Mar 29 12:49 aclocal.m4 drwxr-xr-x 2 vikram vikram 4096 Mar 29 12:49 autom4te.cache drwxrwxr-x 2 vikram vikram 4096 Mar 29 12:49 build -rwxr-xr-x 1 vikram vikram 42037 Mar 29 12:49 config.guess -rw-rw-r-- 1 vikram vikram 1565 Mar 29 12:49 config.h.in -rw-r--r-- 1 vikram vikram 3297 Nov 20 19:14 config.m4 -rwxr-xr-x 1 vikram vikram 30253 Mar 29 12:49 config.sub -rwxrwxr-x 1 vikram vikram 666346 Mar 29 12:49 configure -rw-rw-r-- 1 vikram vikram 2691 Mar 29 12:49 configure.in -rwxr-xr-x 1 vikram vikram 508 Jul 11 2006 config.w32 -rw-r--r-- 1 vikram vikram 182086 Nov 21 11:56 ibm_db2.c -rw-r--r-- 1 vikram vikram 4687 Aug 31 2005 ibm_db2.dsp -rw-rw-r-- 1 vikram vikram 0 Mar 29 12:49 install-sh -rw-r--r-- 1 vikram vikram 196440 Mar 29 12:49 ltmain.sh -rw-r--r-- 1 vikram vikram 4308 Mar 29 12:49 Makefile.global -rw-rw-r-- 1 vikram vikram 0 Mar 29 12:49 missing -rw-rw-r-- 1 vikram vikram 0 Mar 29 12:49 mkinstalldirs -rw-r--r-- 1 vikram vikram 7993 Nov 20 19:06 php_ibm_db2.h -rw-r--r-- 1 vikram vikram 54642 Mar 29 12:49 run-tests.php drwxrwxr-x 2 vikram vikram 4096 Mar 29 12:47 tests -rw-r--r-- 1 vikram vikram 256 Nov 20 19:06 TODO
$ ./configure --with-IBM_DB2=<path to DB2 install dir> $ make # make install
Specify path to DB2 e.g. /home/db2inst1/sqllib or /opt/IBM/db2/V9.5 or your version of DB2