Advanced Cursors in DB2

                             CURSORS IN DB2

Use of DB2 Cursors in Application Program

• Mechanism that allows the SELECT statement to find a set of rows from table
• It return rows one at a time
• Used as similar to a File which can be Opened, Read and Closed
• ‘Fetch’ terminology is used instead of ‘Read’
• Can be declared in both ‘Working Storage’ and ‘Procedure Division’
• Should always be declared before the ‘Open’ Cursor statement

Types of Cursors

- Row Positioned Cursor
- RowSet Positioned Cursor
- Scrollable
- Non Scrollable
- Held
- Non-Held
- Returnable
- Non-Returnable

 

Brief Description for cursors 

DB2 Performance Tips - EXPLAIN

DB2 EXPLAIN Explained

What is DB2 Explain?

It is an DB2 aid for optimization.

What does DB2 Explain does?

All the SQL statements are passed through DB2 optimizer and the access path are externalized. This means any DBA doing performance test can get correct access path by using EXPLAIN. All the access path are recorded in PLAN_TABLE.

What is PLAN_TABLE?

Important coloumns for PLAN_TABLE are as following:

QUERYNO : This is the query number user should assigned to identify query.
QBLOCKNO:  Position of query in the statement being EXPLAINED
APPLNAME : This is PLAN Name
PROGNAME : Program/Package Name
METHOD : There are 4 options and each number signifies type of join

0 Table Access

1 Nested Loop

2 Merge Scan

3 Sort needed by ORDER BY, GROUP BY, SELECT, DISTINCT or UNION

4 Hybrid
CREATOR : User id
TNAME : Tablename
TABNO : position and reserved for IBM only
ACCESSTYPE : 

I - by index

N - indexscan when predicate is IN

R - Table Space Scan

M - Multiple Index Scan

MX - Index Scan

MI - Intersection of multiple index

MU - Union of multiplex index
MATCHCOLS : number of keys used in index scan

ACCESSCREATOR : Creator of Index

ACCESSNAME : Name of Index

INDEXONLY : Y/N ( Yes is index is sufficient to get required data)

SORTN_UNIQ : Y/D (Yes if Sort performed to remove duplicates )

SORTN_JOIN : Sort is performed on table when method is 2 or 4

SORTN_ORDERBY : if query results in order by 

SORTN_GROUPBY : if query results in group by

TSLOCKMODE : Lock Mode of table or tablespace

IS : Intent Share Lock

IX : Intent Exclusive Lock

S : Share Lock

U : Update Lock

X : Exclusive Lock

SIX:  Share with intent exclusive lock

N : UR isolation; no lock

TIMESTAMP : Timestamp at which explain statement is bound

PREFETCH : List Prefetch or Sequential Prefetch

MIXOPSEQ : Sequence

DB2 performance tips - PART 1

DB2 Queries that can optimize performance 

1. Select columns which are required
2. Use where clause for queries where ever possible
3. Avoid using NOT in Queries as NOT is non-indexable
4. Use DATE Functions wherever possible to get number of days.
       Eg: Select DATE('25-04-1986') - DATE('04-09-1995')
5. Details on Index and EXPLAIN:

Before and After creating index always use EXPLAIN functionality of DB2 to measure how   and index is working.If the index you created is being used then you will get that in your query once explain runs.

If you want to see when your index is last used you can do that by this query:

SELECT * FROM SYSIBM.SYSINDEXES where NAME = 'your index name';

I will give details on EXPLAIN in my next DB2 blog.

Introduction to most useful SYSIBM DB2 tables - PART 2

Here comes some more SYSIBM tables

SYSIBM.SYSSTMT : This table is input to bind process. It contains statements for every plan known in DB2.

SYSIBM.SYSPLANS: contains information on every plan known to DB2. The plan name is unique in the DB2 subsystem.

SYSIBM.SYSFOREIGNKEYS: contains information foreign keys for table. 

http://supportline.microfocus.com/documentation/books/sx51/rhsqst12.htm

SYSIBM.SYSDBAUTH: Contains information on database level authorizations for each user of each database.  Entries are made into this table when database-related GRANT or REVOKE commands are issued for the location.

http://supportline.microfocus.com/documentation/books/sx51/rhsqst0y.htm

Please refer PART1 link to get more information on SYSIBM tables.

Options of CICS DB2 connections

Options of CICS DB2 connections 

There are three main types of threads that can  be used for DB2 CICS connection:

TYPE=COMD
TYPE=POOL
TYPE=ENTRY

COMD is DB2 command thread it is used for only processing DB2 commands through DSNC transaction.  It is not used for CICS attachment facility.

POOL is Pool threads are used for all transactions and commands not using an entry type or a DB2 command type. Pool threads are normally used for: 

1. Low volume transactions
2. Overflow transactions  from both COMD and ENTRY type threads 
3. It is terminated immediately when it is unused

ENTRY is ENtry type thread which is used for: 

1. High volume transactions 
2. High priority transactions 
3. Controlled transactions 

ENTRY type can be defined as protected as well as unprotected.The MVS subtask for an entry thread is not terminated, even if the entry thread is terminated. This is true for both protected and unprotected entry threads. 
Requests for an entry thread can be transferred to the pool, if an entry thread is not available.

Introduction to most useful SYSIBM DB2 tables - PART 1

Here are most commonly used SYSIBM tables

SYSIBM.SYSTABLES : Any table or view or alias defined for your application will be present in this table with all the information.

Here I have got great links for tables where you can get details for each column of  SYSIBM tables. For each table there is separate link I have put them all together for quick reference.

http://supportline.microfocus.com/Documentation/books/sx51/rhsqst2l.htm

SYSIBM.SYSCOLUMNS: All the details about columns for any table along with characteristics of each column is defined here.

http://supportline.microfocus.com/Documentation/books/sx51/rhsqst0s.htm

DB2 - Locks

How DB2 locks works?

DB2 Database Manager implicitly acquires locks as they are need, except for occasions where the Uncommitted Read isolation level is used.

It is possible for the DB2 Database Manager to acquire row-level locks or table-level locks on a specific table resource by executing a special form of the ALTER TABLE SQL statement. By default, the DB2 Database Manager always attempts to acquire row-level locks.

  

 The syntax is ALTER TABLE [Table Name] LOCKSIZE [ROW | TABLE] 

 E.g. ALTER TABLE school LOCKSIZE TABLE

Compile new DB2 program in Changeman

Add new DB2 program in Changeman

Point to take care while creating new DB2 program in changeman and to promote it to test regions

Create a PKG member in the package where a new DB2 program is present. This member is required in order for DBRM to be promoted to the test region. If this member is not present, changeman gives error while promoting DBRM.

          BIND PACKAGE      (PVNPRBDL) +

                MEMBER          (PVNBPBOT) + 

                OWNER            (DABOC) + 

                QUALIFIER       (PVNDB001) + 

                ACTION           (REPLACE) + 

                DEGREE          (1) +  

                ISOLATION      (CS) + 

                VALIDATE       (BIND) + 

                EXPLAIN         (YES) +  

                 RELEASE       (COMMIT) + 

                CURRENTDATA  (YES) 

DB2 Bind Process: Overview

The bind process

 The bind process establishes a relationship between an application program and its relational data. This step is necessary before you can execute your program. Currently, DB2 allows you two basic ways of binding a program: to a package, or directly to an application plan.

Even when they are bound into packages, all programs must be designated in an application plan. BIND PLAN establishes the relationship between DB2 and all DBRMs or packages in that plan. Plans can specify explicitly named DBRMs, packages, collections of packages, or a combination of these elements. The plan contains information about the designated DBRMs or packages and about the data the application program intends to use. It is stored in the DB2 catalog.

JCLs to Alter Lock parameter of DB2 table

   

                         JCLS TO ALTER LOCK PARAMETERS OF ANY TABLE (DB2 V 9)

LOCKPART

JCL to alter tablespace lockpart parameter (only for partitioned) to NO in DB2

//PRODDB2  EXEC PGM=IKJEFT01,DYNAMNBR=30                

//STEPLIB  DD DSN=SYS1.DSN900.SDSNLOAD,DISP=SHR         

//SYSTSPRT DD SYSOUT=X                                  

//SYSTSIN  DD *                                         

    DSN SYSTEM(DB2PROD)                                    

          RUN PROGRAM(DSNTEP) PLAN(DB2PLAN) -         

          LIBRARY('DSN.DB2PROD.RUNLIB.LOAD')            

      END                                               

//SYSPRINT DD SYSOUT=*                                  

//SYSUDUMP DD SYSOUT=*                                  

//SYSIN    DD *                                         

ALTER TABLESPACE PRDX1.TSSMG                            

LOCKPART NO;                                            

COMMIT;                                                 

This will give problem as tablespace is not stopped. it will give following error:

PAGE    1                                                                      

***INPUT STATEMENT:                                                             

 ALTER TABLESPACE PRDX1.TSSMG                                                   

 LOCKPART NO;                                                                  

SQLERROR ON   ALTER     COMMAND, EXECUTE   FUNCTION                            

 RESULT OF SQL STATEMENT:                                                      

 DSNT408I SQLCODE = -626, ERROR:  THE ALTER STATEMENT IS NOT EXECUTABLE BECAUSE  THE PAGE SET IS NOT STOPPED 

 DSNT418I SQLSTATE   = 55015 SQLSTATE RETURN CODE                               

 DSNT415I SQLERRP    = DSNXIATS SQL PROCEDURE DETECTING ERROR                  

 DSNT416I SQLERRD    = 155  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION          

 DSNT416I SQLERRD    = X'0000009B'  X'00000000'  X'00000000'  X'FFFFFFFF'  X'000

          INFORMATION                                                          

PAGE    1                                                                      

***INPUT STATEMENT:                                                            

 COMMIT;                                                                        

 RESULT OF SQL STATEMENT:                                                      

 DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION                                 

 DSNT418I SQLSTATE   = 00000 SQLSTATE RETURN CODE                              

 DSNT416I SQLERRD    = 0  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION            

 DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000000'  X'FFFFFFFF'  X'000

SYSIBM entries for TableSpace Partitioned Table

STEPS TO CHECK ALL THE TABLESPACE PARTITIONED ENTRIES

1. Tables are always defined in respective tablespaces 

SELECT * FROM SYSIBM.SYSTABLES     
WHERE NAME=  'MSG_RCVD'         
WITH UR;                           

The tablespace for the mentioned table is TSMPR
----------------------------------------------------------------------------------------

2. The table space is partitioned by defining index on each partition with Limit keys.

SELECT * FROM SYSIBM.SYSTABLESPACE      
WHERE NAME  = 'TSMPR'                 
WITH UR;                                

The partition we get from here were 

DB2 Abends while loading unloading tables

Here are some common abends faced while running DB2 load unload.

Issue: The error shows input field is not matching the data or vice versa. Key word here is 'INPUT FIELD 'ACCT_3' NOT ENTIRELY WITHIN INPUT RECORD'

DSNURWI - TABLE SPACE DatabaseName.tablespace WILL REMAIN IN BASIC ROW FORMAT DUE TO 

SPRMRRF=DISABLE

DSNURRST - EXISTING RECORDS DELETED FROM TABLESPACE                            

DSNURWBG - INPUT FIELD 'ACCT_3' NOT ENTIRELY WITHIN INPUT RECORD          

DSNURWBF - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED=1     

GBAC - UTILITY DATA BASE SERVICES MEMORY EXECUTION ABENDED, REASON=X'00E40323' 

DB2 Commands for Table Space

Access Commands

Steps to  go DB2 Commands panel:

 

Go to DB2 DATA BASE INTERACTIVE FUNCTIONS MENU Go to DB2I PRIMARY OPTION Select MENU in the list (It is generally at position 1).