Category: DB2

DIRECT YES and DIRECT NO options in BMC unload plus

DIRECT YES:

When we use DIRECT YES option, BMC Unload Plus utility (ADUUMAIN) reads data directly from the table space data set or image copy data set and uses a SELECT-like syntax for data selection.

We can unload all data from a table-space without requiring written SELECT statements.

The data can be sorted by clustering key or partitioning key.

DIRECT NO:

When we use DIRECT NO option, BMC Unload Plus utility uses DB2 dynamic SQL to process the SELECT statement.

Data type conversions can be performed using DIRECT NO (which is not available with DIRECT YES).

 

Advertisement

DB2 commands : TERM, START, STOP, DIS

– DIS or -Display : Display a status of tablespace (ie READ-WRITE / COPY-PENDING / CHECK-PENDING / READ ONLY)

-DIS  DATABASE(dbname) SPACENAM(tbspace)

more on Display Database

Stop / Start a tablespace / indexspace:

-STOP  DATABASE(dbname) SPACENAM(tbspace)
-START  DATABASE(dbname) SPACENAM(tbspace) ACCESS(FORCE)

Start: publib.boulder.ibm.com/../bjncstdb999243.htm

Stop: publib.boulder.ibm.com/../bjncstod999243.htm
How to find a table’s Database name and tablespace name?

SELECT DBNAME, TSNAME FROM
SYSIBM.SYSTABLES WHERE NAME = [table name]
 AND OWNER = [qualifier] ;

SELECT DBNAME, TSNAME FROM
SYSIBM.SYSTABLES WHERE NAME = 'SYSDUMMY1' AND
OWNER = 'SYSIBM';

Terminating a BMC (or any DB2) utility:
issue the Terminate Utility command:
-TER UTILITY([util id])
TERM: publib.boulder.ibm.com/../ctermu.htm

Logical Operators in DB2

Functionality Operator
Logical AND <condition 1> AND <condition 2>
Logical OR <condition 1> OR <condition 2>
Logical NOT NOT <condition>
Greater than <value 1>  > <value 2>
Less than <value 1>  < <value 2>
Greater than or equal to <value 1>  >= <value 2>
Less than or equal to <value 1>  <= <value 2>
Not Eqaul <value 1>  <> <value 2>
Between <variable> BETWEEN <value 1> AND <value 2>

Embedded DB2 SQL and Cursor in Easytrieve

SQL statements in Easytrieve should be prefixed with the word, SQL.
For eg,

 SQL SELECT 1 FROM +
 SYSIBM.SYSDUMMY1 INTO :WS-INT

+ statement is used for statement continuation in the next line.

Host variable datatype mapping between Easytrieve and DB2.

DB2 Datatype Number of Bytes Easytrieve variable
INTEGER 4 WS-INT W 4 B
SMALLINT 2 WS-SMALLINT W 2 B
DATE 10 WS-DATE W 10 A
CHAR(25) 25 WS-CHAR-25 W 25 A
TIMESTAMP 26 WS-TIMESTAMP W 26 A
NULL INDICATOR 2 WS-NULLIND W 2 B
DECIMAL(16,2) 8 WS-DECI1 W 8 P 2
DECIMAL(16,5) 8 WS-DECI2 W 8 P 5
DECIMAL(17,5) 9 WS-DECI3 W 9 P 5

How to get a SQL’s return code?

Use the SQLCODE variable to check the DB2 SQL return code.

PARM SSID('DBXX')
 EMP-ID   W  6 N
 EMP-NAME W 25 A
 JOB INPUT NULL
 SQL                              +
  SELECT EMP_ID, EMP_NAME FROM    +
  EMPLOYEE INTO                   +
  :EMP-ID, :EMP-NAME
  CASE SQLCODE
  WHEN +0
     DISPLAY 'EMP_ID :' EMP-ID
     DISPLAY 'EMP_NAME :' EMP-NAME
  WHEN +100
     DISPLAY 'NO ROW FOUND'
  OTHERWISE
     DISPLAY 'ERROR. SQLCODE ' SQLCODE
 END-CASE
 STOP

How to Run an Update query and get the number of rows updated?

SQLERRD(3) = number of rows updated from an Update query or deleted by a delete query

PARM SSID('DBXX')
 JOB INPUT NULL
 SQL                              +
  UPDATE EMPLOYEE                 +
   SET SALARY = SALARY * 1.05     +
   WHERE EMP_ID = 113114
*
  DISPLAY 'SQLCODE IS   : ' SQLCODE
  DISPLAY 'ROWS UPDATED : ' SQLERRD(3)
*
 STOP

How to declare a Cursor and Fetch records?

PARM SSID('DBXX')
 EMP-ID   W  6 N
 EMP-NAME W 25 A
*
 SQL DECLARE C1 CURSOR FOR        +
  SELECT EMP_ID, EMP_NAME FROM    +
  EMPLOYEE                        +
  WHERE EMP_ID > 5
*
 JOB INPUT NULL
 SQL OPEN C1
 DISPLAY ' SQL CODE : ' SQLCODE
*
 DO WHILE SQLCODE NE +100
    SQL FETCH C1 INTO             +
          :EMP-ID                 +
         ,:EMP-NAME

    DISPLAY ' SQL CODE : ' SQLCODE
    IF SQLCODE NE 0 AND SQLCODE NE 100
       DISPLAY 'ERROR : SQLCODE : ' SQLCODE
       STOP
    END-IF
    IF SQLCODE EQ 0
       DISPLAY 'EMP-ID      :' EMP-ID
       DISPLAY 'EMP-NAME    :' EMP-NAME
    END-IF
 END-DO
*
 SQL CLOSE C1
 DISPLAY ' SQL CODE : ' SQLCODE
*
 STOP

Also consider reading

http://www.angelfire.com/folk/anoop/easydb2.pdf

http://www.mvsforums.com/helpboards/viewtopic.php?t=478

http://ibmmainframes.com/about27446.html

Multi Row Fetch (Thanks to Roy for providing this link)

http://www.hiperformance-software.de/documents/DB2%20V8%20Multiple%20Row%20Fetch%20-%20Concepts%20V22.pdf

Using MAXASSIGNEDVAL from SYSIBM.SYSSEQUENCES (IBM DB2 for Z OS)

SYSIBM.SYSSEQUENCES has a column called MAXASSIGNEDVAL, that gets assigned whenever the values for IDENTITY/SEQUENCE columns are getting generated (at least for the cache).

SELECT MAXASSIGNEDVAL 
FROM SYSIBM.SYSSEQUENCES
WHERE SEQUENCEID = 
(SELECT BSEQUENCEID FROM SYSIBM.SYSSEQUENCESDEP
WHERE DCREATOR = 'USER1'
AND DNAME = 'EMP_TABLE'
AND DCOLNAME = 'EMP_ID');

Creating (Declaring), Inserting data into DB2 temporary tables

Declaring (Creating)


DECLARE GLOBAL TEMPORARY TABLE
SESSION.EMP_TABLE
(EMPID INTEGER NOT NULL,
EMPNAME CHAR(20))
[ON COMMIT DELETE ROWS  | ON COMMIT PRESERVE ROWS]

Even if you do not give the qualifier SESSION, the table will be created as SESSION.EMP_TABLE.
Temporary tables cannot be created with our own qualifier.

Inserting

INSERT INTO SESSION.EMP_TABLE VALUES(1,'KARTHIK')

Fetching

SELECT * FROM SESSION.EMP_TABLE

Using IKJEFT01 for Binding a normal batch DB2 program

//BINDSTEP EXEC PGM=IKJEFT01
//DBRMLIB  DD  DSN=my.dbrm.library,DISP=SHR
//STEPLIB  DD DISP=SHR,
//            DSN='SSID.SDSNLOAD'
//SYSTERM  DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
DSN SYSTEM(SSID) RETRY(2) TEST(0)
  BIND PACKAGE   (PKGNAME)  -
       MEMBER    (PGMNAME) -
       OWNER     (USER1)  -
       QUALIFIER (USER1)  -
       ACTION    (REPLACE)  -
       VALIDATE  (BIND)     -
       ISOLATION (CS)       -
       EXPLAIN   (YES)      -
       RELEASE   (COMMIT)
  END
/*

Using IKJEFT01 for running a normal batch DB2 program

//STEP001  EXEC PGM=IKJEFT01
//*
//STEPLIB  DD DISP=SHR,DSN=SSID.SDSNLOAD
//*
//DISPLAY  DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSABOUT DD SYSOUT=*
//SYSDBOUT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN  DD *
    DSN SYSTEM(SSID)
    RUN PROGRAM(PGMNAME) PLAN(PLANNAME) PARM('ANY PARM TO THE PGM') -
    LIB('MYPGM.LOADLIB')
    END
/*

Running DSNTIAUL using IKJEFT01 for batch unload

DSNTIAUL (an assembler program used to process select statements dynamically) is used to unload the tables, or to get the results of an SQL select in the unload format.

No details about the query will be displayed, just the resultset will be in the SYSPRINT.

Unloading the whole table. 

//STEP0001 EXEC PGM=IKJEFT01
//STEPLIB  DD DISP=SHR,
//            DSN='SSID.SDSNLOAD'
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD DISP=(NEW,CATLG,DELETE),
//            SPACE=(CYL,(250,100),RLSE),
//            UNIT=DISK,
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=0),
//            DSN=TEST.MYDSN
//SYSUDUMP DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//SYSTSOUT DD SYSOUT=*
//SYSTSIN  DD *
  DSN SYSTEM(SSID)
  RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL)
  LIB ('SSID.SDSNLOAD')
/*
//SYSIN    DD *
SYSIBM.SYSDUMMY1
/*

Unloading a specific SQL select.

//STEP0001 EXEC PGM=IKJEFT01
//STEPLIB  DD DISP=SHR,
//            DSN='SSID.SDSNLOAD'
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD DISP=(NEW,CATLG,DELETE),
//            SPACE=(CYL,(250,100),RLSE),
//            UNIT=DISK,
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=0),
//            DSN=TEST.MYDSN
//SYSUDUMP DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//SYSTSOUT DD SYSOUT=*
//SYSTSIN  DD *
  DSN SYSTEM(SSID)
  RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL,1') –
  LIB ('SSID.SDSNLOAD')
/*
//SYSIN    DD *
SELECT * FROM SYSIBM.SYSDUMMY1
/*

The Parm ‘SQL,1’ says that I am giving an SQL statement and fetch only one record from the resultset into SYSPRINT.

Running DSNTEP2 / DSNTEP4 using IKJEFT01 for batch SQL(DB2) processing

The output of these utilities will be exactly like executing a Spufi. It will have the complete details of the query and formatted columns. If you just want the resultset (in the unload format) Please read about the DSNTIAUL utility.

DSNTEP2 / DSNTEP4: These programs are used to execute the SQL queries in batch.

The output (SYSPRINT) will have complete details of the query execution.
DSNTEP2 is a PL/I program used to process SELECT and NON SELECT statements dynamically.

//STEP0001 EXEC PGM=IKJEFT01 ,DYNAMNBR=20
//STEPLIB  DD DISP=SHR,
//            DSN='SSID.SDSNLOAD'
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD DISP=(NEW,CATLG,DELETE),
//            SPACE=(CYL,(250,100),RLSE),
//            UNIT=DISK,
//            DCB=(RECFM=FB,LRECL=133,BLKSIZE=0),
//            DSN=TEST.MYDSN
//SYSUDUMP DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//SYSTSOUT DD SYSOUT=*
//SYSTSIN  DD *
  DSN SYSTEM(SSID)
  RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) PARM('ALIGN(LHS)')
  LIB ('SSID.SDSNLOAD')
/*
//SYSIN    DD *
 SELECT * FROM SYSIBM.SYSDUMMY1
/*

The Parm for this program is optional.

Valid values are,

ALIGN(MID) – center alignment

ALIGN(LHS) – left alignment

MAXSEL(n) – to limit the number of rows returned from a
SELECT statement to n (n = 0 -> 32768)
NOMIXED – ??

MIXED  – ??

SQLTERM(termchar) – termchar is the character that you use to end each SQL
| statement.