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



JES, JES2, JCL utilities, IDCAMS, Compile & Run JCLs, PROCs etc...

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

Postby dkuma14 » Fri Oct 14, 2016 12:24 am

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
dkuma14
 
Posts: 15
Joined: Thu Oct 13, 2016 7:34 pm
Has thanked: 7 times
Been thanked: 0 time

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

 

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

Postby Akatsukami » Fri Oct 14, 2016 3:19 am

You couldn't possibly have used that JCL. Try again, but use the real unload utility this time.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1053
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

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

Postby dkuma14 » Fri Oct 14, 2016 4:10 am

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
dkuma14
 
Posts: 15
Joined: Thu Oct 13, 2016 7:34 pm
Has thanked: 7 times
Been thanked: 0 time

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

Postby Akatsukami » Fri Oct 14, 2016 4:27 am

: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).
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day

These users thanked the author Akatsukami for the post:
dkuma14 (Fri Oct 14, 2016 7:25 pm)
User avatar
Akatsukami
Global moderator
 
Posts: 1053
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

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

Postby NicC » Fri Oct 14, 2016 6:39 pm

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.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

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

Postby enrico-sorichetti » Fri Oct 14, 2016 6:42 pm

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 )
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort

These users thanked the author enrico-sorichetti for the post:
dkuma14 (Fri Oct 14, 2016 7:25 pm)
enrico-sorichetti
Global moderator
 
Posts: 2644
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

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

Postby dkuma14 » Fri Oct 14, 2016 7:23 pm

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.
You do not have the required permissions to view the files attached to this post.
dkuma14
 
Posts: 15
Joined: Thu Oct 13, 2016 7:34 pm
Has thanked: 7 times
Been thanked: 0 time

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

Postby dkuma14 » Fri Oct 14, 2016 7:28 pm

@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.
dkuma14
 
Posts: 15
Joined: Thu Oct 13, 2016 7:34 pm
Has thanked: 7 times
Been thanked: 0 time

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

Postby Akatsukami » Fri Oct 14, 2016 7:36 pm

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
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1053
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

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

Postby enrico-sorichetti » Fri Oct 14, 2016 7:38 pm

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
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2644
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

Next

Return to JCL

 


  • Related topics
    Replies
    Views
    Last post