Page 1 of 2

JCL to unload a DB2 table having C and L type fields.

PostPosted: Fri Oct 14, 2016 12:24 am
by dkuma14
Hello Everyone.

I need to unload all fields of a table using Jcl In a Single PS file as output. Table has C or TSI or L type of fields ( three types of fields are there in table)

I used below Jcl to unload a table whose fields types are either C or TSI only and all fields got uloaded into output file without an issue.

But when I am using the same query to unload the table which also have field type as 'L' then Jobs are getting completed and even fields are getting unloaded into output file except the fields whose type is 'L' .Job is not giving any error its getting completed byt Two fields whose type are L not getting unloaded into output file.

Please provide a Jcl which will unload all fields of that table into a single output file.

At the bottom I have mentioed how that table fields looks like.

///***********UNLOAD JCL WHICH I HAVE USED***********///
//*STEP020 EXEC PGM=IKJEFT01,DYNAMNBR=20
//*STEPLIB DD   DSN=IC***.RUNLIB.LOAD,DISP=SHR
//*SYSTSPRT DD  SYSOUT=*
//*SYSTSIN   DD *
//*  DSN SYSTEM(DB**)
//*  RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARM ('SQL')
//*  END
//*SYSPRINT DD  SYSOUT=*
//*SYSDUMP  DD  SYSOUT=*
//*
//*SYSREC00 DD DSN=IC***.*******.OUTPUT,
//*            DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,
//*            DCB=(RECFM=FB,LRECL=800,BLKSIZE=8000),
//*            SPACE=(1470,(100,50),RLSE)
//*SYSPUNCH DD DUMMY,
//*            DCB=(LRECL=135,BLKSIZE=1485,RECFM=FB)
//*SYSIN    DD *
     SEELECT *
 FROM DB2.table name.
with ur;
/*
 


///*****************************************TABLE FIELDS DETAILS***************************************

NUM COLUMN HEADING                            WIDTH     EDIT     SEQ
 --- ---------------------------------------- ------- ------ ----- ----- ---
1         DIV_NO                                            3           C         1
2         PRD_NO                                           5           C         2
3         PLN_DTL_TYP_CD                            3           C         3
4         LN_NO                                            2           C         4
5         SBL_NO                                           3           C         5
6         SBL_VAR_NO                                   3          C          6
7         MTH_RNG_QT                                 6          L          7
8         TOT_DAYS_AVL_NO                         6          L         8
9         PRD_DS                                           25        C         9
10      USR_LST_UPD_CD                              7         C        10
11       LST_MTC_TS                                     26     TSI         11
12       CRT_ASC_CD                                     7        C          12
13       CRT_TS                                             26     TSI         13
 

Please provide jcl If anyone have. I am not able to unload fields MTH_RNG_QT and TOT_DAYS_AVL_NO while using my above mentioned jcl (at top).
Have also attached table fields detail if it is not clear here.
Thanks.
Coded, attachment deleted

Re: JCL to unload a DB2 table having C and L type fields.

PostPosted: Fri Oct 14, 2016 3:19 am
by Akatsukami
You couldn't possibly have used that JCL. Try again, but use the real unload utility this time.

Re: JCL to unload a DB2 table having C and L type fields.

PostPosted: Fri Oct 14, 2016 4:10 am
by dkuma14
Thanks for your response .

As I mentioned earlier that above Jcl worked successfully for me to unload a table which have field type as character only.

Now when I am using this same Jcl for unloading a table which also have CLOB type field then that field is not coming in output unload file although job is completing successfully.

Do you know about any other utility which can unload CLOB type and character type fields from a table in one go into a single output file ??

Thanks

Re: JCL to unload a DB2 table having C and L type fields.

PostPosted: Fri Oct 14, 2016 4:27 am
by Akatsukami
:roll: Yes, the real DB2 utility DSNUTILB.

Here is the official documentation describing its invocation through JCL. Here is the official documentation on writing the UNLOAD statement (which is not JCL).

Re: JCL to unload a DB2 table having C and L type fields.

PostPosted: Fri Oct 14, 2016 6:39 pm
by NicC
The "above JCL" did not do anything as all the JCL statements are, in fact, comments. And I doubt if SEELECT is a valid DB2 verb so you should have had an error.

Re: JCL to unload a DB2 table having C and L type fields.

PostPosted: Fri Oct 14, 2016 6:42 pm
by enrico-sorichetti
did You care to read the db2 manuals on how to unload LOBS ?
did You also speak to Your support for the standards used in Your organisation for such tasks
( dealing with LOBS is pretty murky )

Re: JCL to unload a DB2 table having C and L type fields.

PostPosted: Fri Oct 14, 2016 7:23 pm
by dkuma14
Please find attached the screenshot having details of fields of that table.

The fields which is marked in red are not getting unloaded in to output file but other fields apart from marked in red are present in output unlload file.

My requirement is to unload all fields value in to a output file from which furtherall records will be send to a txt.file or excel sheet. As all fields values are required to send
I am looking for a Jcl unload job which will unload all fields from that table.

Thanks.

Re: JCL to unload a DB2 table having C and L type fields.

PostPosted: Fri Oct 14, 2016 7:28 pm
by dkuma14
@Nicc Thanks for your observation that all the lines of Jcl are commented out, By mistake I posted that as commented line.But that is not the concern for this issue, I have used same Jcl without making it as comment line and then I am getting the Issue what I have mentioed.

Re: JCL to unload a DB2 table having C and L type fields.

PostPosted: Fri Oct 14, 2016 7:36 pm
by Akatsukami
None of those fields are *LOBs. You have one more opportunity to tell what you''re really doing, and then I lock this thread :x

Re: JCL to unload a DB2 table having C and L type fields.

PostPosted: Fri Oct 14, 2016 7:38 pm
by enrico-sorichetti
quite a bit of confusion around here ...

just take a decision,
are You complaining that LOBS are not unloaded
or some other field is not unloaded

clarify better the environment the requirements and the results

and again RTFM