kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
 Posted: Tue Feb 18, 2003 2:18 pm Post subject: |
 |
|
Bindu,
The following JCL will give you the total no: of records for the database in question. A brief explanation of the job.I chose the batch appraoch since the interactive solution may tie up your session.
Step0100 creates the select cards for the next step. In this step we query sysibm.systables for the tables under the particular database.For each table we generate a sql statement like this
| Code: |
SELECT COUNT(*) AS RECCNT FROM CREATOR.TABLE1 UNION
|
Here I used creator.table name as that is the standard of our shop. so if you have a different standard then change the control card where it says creator.for ex: if your shop has a standard as DBNAME.TABLENAME
Then in step0100 change the 'CREATOR' to DBNAME
now the step0200 takes in these created control cards and does a sum on the total values.
we need to subtract 1 from the count as we are also including the record count from sysibm.sysdummy1. This has to be used inorder to properly end the sql syntax.
The output from step0100 looks like this
| Code: |
SELECT COUNT(*) AS RECCNT FROM CREATOR.TABLE1 UNION
SELECT COUNT(*) AS RECCNT FROM CREATOR.TABLE2 UNION
.....
SELECT COUNT(*) AS RECCNT FROM CREATOR.TABLEN UNION
SELECT COUNT(*) AS RECCNT FROM SYSIBM.SYSDUMMY1) A;
|
In step0200 we are adding another sql statement for summing all the counts
| Code: |
SELECT SUM(A.RECCNT) -1 FROM (
|
| Code: |
//STEP0100 EXEC PGM=IKJEFT01
//*
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(XXXX)
RUN PROGRAM(DSNTIAUL) -
PLAN(DSNTIAUL) -
PARMS('SQL') -
LIB('DB2P.RUNLIB.LOAD')
//SYSREC00 DD DSN=&T1,DISP=(,PASS),UNIT=SYSDA,SPACE=(CYL,(5,5),RLSE)
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT CHAR('SELECT COUNT(*) AS RECCNT FROM ') CONCAT
CHAR(CREATOR) CONCAT
CHAR('.') CONCAT
CHAR(NAME) CONCAT
CHAR(' UNION ') CONCAT
CHAR(' ')
FROM
SYSIBM.SYSTABLES
WHERE DBNAME='YOUR DBNAME'
AND TYPE = 'T'
UNION
SELECT CHAR('SELECT COUNT(*) AS RECCNT FROM SYSIBM.SYSDUMMY1) A;')
FROM
SYSIBM.SYSDUMMY1
;
//STEP0200 EXEC PGM=IKJEFT01
//*
//SYSTSIN DD *
DSN SYSTEM(XXXX)
RUN PROGRAM(DSNTEP2) -
PLAN(DSNTEP2) -
LIB('DB2P.RUNLIB.LOAD')
END
//*
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
SELECT SUM(A.RECCNT) - 1 FROM (
// DD DSN=&T1,DISP=(OLD,PASS)
//*
|
The sysprint dataset will have the count of total records in the database
Hope this helps....
cheers
kolusu |
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
