Finding DB2 table length through REXX

[vinoth4u]

Hello All,

I am doing REXX code to unload data from the DB2 table.Hence i need to know the length of the table so that the unloaded data will fit exactly into the output file.

Could someone please help me on how to find out the length of the input table given by user ?


Thanks & Regards,
Vinoth KM

[nicc]

If you want help it will improve your chances if you post a rexx question in the rexx part of the forum and not in the cobol part! I have moved it for you.

What do you mean by 'length of the table'? The number of rows (query the table first using the COUNT facility)? Or something else?
What do you mean 'fit exactly into the output file'? it is a dataset not a file. Datasets can expand, through secondary allocation, or contract, through RLSE parameter, as required.
How are you doing the unload? Via a DB2 utility called by your Rexx program?

[vinoth4u]

"Datasets can expand, through secondary allocation, or contract, through RLSE parameter" helps me to achieve what i want .Thanks Nicc

Thanks & Regards,
Vinoth KM

[Anuj Dhawan]

Hi Vinoth,

What are you using to unload the DB2 Table - is it DSNTIAUL or some unload utility available at your shop? Are you using DSNREXX to get connected with DB2 or you've a JCL which is executed using REXX?

If you are using DSNTIAUL and calling it using REXX, you can use the below JCL. You can notice that you can remove the LRECL for SYSREC00 DD. This can be taken care by DSNTIAUL itself. (In the JCL below, you've to replace xxxx with appropriate values for your shop).

Code: Select all

//UNLOAD  EXEC PGM=IKJEFT01,DYNAMNBR=20                   
//SYSTSPRT DD  SYSOUT=*                                   
//SYSTSIN  DD  *                                          
DSN SYSTEM(xxxx)                                          
RUN  PROGRAM(DSNTIAUL) PLAN(xxxx) -                   
     PARMS('SQL') LIB('xxxx')               
/*
//SYSPRINT DD SYSOUT=*                                    
//SYSUDUMP DD SYSOUT=*                                    
//*
//* Table Unload
//SYSREC00 DD DSN=USERID.TABLE.UNLOAD,                        
//            UNIT=SYSDA,                                  
//            DISP=(NEW,CATLG,DELETE),                              
//*
//SYSPUNCH DD DSN=USERID.SYSPUNCH,                        
//            UNIT=SYSDA,                                 
//            SPACE=(xxxx,(xxxx,xxxx)), 
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=27920),     
//            DISP=(NEW,CATLG,CATLG)                     
//SYSIN    DD *                                          
           SELECT *
           FROM  TABLE_NAME
           WITH UR;
/*
//*

On the other hand if you show us the REXX you are using, there might be better chances to understand what exactly might work in your case.

[vinoth4u]

Hi Anuj,

Thanks a lot for your clear explanation.

Yeah,I am using "DSNTIAUL" and i have removed LRECL from the SYSREC00 and i am good now.


Thanks & Regards,
Vinoth KM

[DB2 Guy]

Can you share your solution for the benefit of others. I am not sure what exactly worked for you as what Nic said and what Anuj has said, are two very different arguments.

[vinoth4u]

Actually my intention in the tool is,

i. User needs to type the "table name & where clause columns" as per their requirement .
ii. Once they done,REXX will take care of unloading the data in the output file and it will be given to the USER.

Here i got the below confusion

1. How jcl knows the LRECL of different tables specified by the USER ?
Anuj clarified the above with "DSNTIAUL".

"If you are using DSNTIAUL and calling it using REXX. You can notice that you can remove the LRECL for SYSREC00 DD.
This can be taken care by DSNTIAUL itself"

Hope this clarifies !!

Thanks & Regards,
Vinoth KM