Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

UNLOAD DS length not the same with table row length, why?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Thu Aug 15, 2013 10:32 am    Post subject: UNLOAD DS length not the same with table row length, why?
Reply with quote

I tried to unload data from a table via below step:
Code:
//*EXECUTE UNLOAD FROM TABLE                         
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20               
//SYSTSPRT DD SYSOUT=*                               
//SYSTSIN DD *                                       
DSN SYSTEM(DD11)                                     
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB10) -               
LIB('DSNDD10S.DD11.RUNLIB.LOAD')                     
//SYSPRINT DD SYSOUT=*                               
//SYSUDUMP DD SYSOUT=*                               
//SYSREC00 DD DSN=TEST.UL@TEST.SYSREC00,             
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG)
//SYSPUNCH DD DSN=TEST.UL@TEST.SYSPUNCH,             
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),     
// RECFM=FB,LRECL=80,BLKSIZE=8000                   
//SYSIN    DD *                                     
TESTQUA.DEVTEST                                     

but after execution, I found the length of dataset : TEST.UL@TEST.SYSREC00
became 60,
while I checked the length of the table row length , I found the row length was 59.
below is the file manager view result of the DCLGEN of the table:
Code:
FILE MANAGER      DEVZBK.TEST.CPY.D130815(DEVTEST)                 Line 1 of 15
                                                                               
1 PRINT LAYOUT                                                                 
                                                                               
Ref Field Name                               Picture   Type Start    End Length
    ****  Top of data  ****                                                   
  1 1 DEVTEST-DESC                                     AN      1     59     59
  2  2 DEVTEST-CHAR-P                       X(6)       AN      1      6      6
  3  2 DEVTEST-SMALLINT-P                   S9(4)      BI      7      8      2
  4  2 DEVTEST-INT-P                        S9(9)      BI      9     12      4
  5  2 DEVTEST-BIGINT-P                     S9(18)     BI     13     20      8
  6  2 DEVTEST-DECIMAL-P                    S9(3)V9(6) PD     21     25      5
  7  2 DEVTEST-CHAR-I                       X(6)       AN     26     31      6
  8  2 DEVTEST-SMALLINT-I                   S9(4)      BI     32     33      2
  9  2 DEVTEST-INT-I                        S9(9)      BI     34     37      4
 10  2 DEVTEST-BIGINT-I                     S9(18)     BI     38     45      8
 11  2 DEVTEST-DECIMAL-I                    S9(3)V9(6) PD     46     50      5
 12  2 DEVTEST-CONTENT                      S9(2)V9(6) PD     51     55      5
 13  2 DEVTEST-LST-UPD-DT                   S9(9)      BI     56     59      4
    ****  End of data  ****                                                   


I viewd the last column of the unloaded dataset, I found it was padded with X'00':
Code:
 sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss
 BROWSE    TEST.UL@TEST.SYSREC00                   LINE 00000000 COL 001 060
********************************* Top of Data **********************************
                                                                               
                                                                               
 ------------------------------------------------------------------------------
insert...................GGGGGG........................... .                   
89A89A0200020000000200000CCCCCC02000200000002000000000003280                   
952593080008000000080000C777777080008000000084000C0000C13BD0                   
 ------------------------------------------------------------------------------
insert...................GGGGGG............... ........... .                   
89A89A0200020000000200000CCCCCC02000200000002090000000003280                   
952593070007000000070000C777777070007000000073000C0000C13BD0                   
 ------------------------------------------------------------------------------


why does this happen?
why the unloaded dataset was padded with X'00' on the last column?

can u please help me on this?

thanks very much.
Back to top
View user's profile Send private message

agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 685
Location: Earth

PostPosted: Thu Aug 15, 2013 7:02 pm    Post subject:
Reply with quote

I haven't experienced this before but I would start by comparing SYSPUNCH offsets with the DCLGEN offsets. Maybe, a new one byte column was added and the DCLGEN you are checking is not the latest one.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Thu Aug 15, 2013 7:10 pm    Post subject:
Reply with quote

Are any of the columns NULLable? If so, then DSNTIAUL has to reserve space for a null indicator.
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 685
Location: Earth

PostPosted: Thu Aug 15, 2013 7:31 pm    Post subject:
Reply with quote

Good point about the null indicator.. although, null indicators preceed the field they belong to. Since the OP is talking about last byte being x'00', I doubt it belongs to a null indicator.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Thu Aug 15, 2013 8:02 pm    Post subject:
Reply with quote

agkshirsagar wrote:
Good point about the null indicator.. although, null indicators preceed the field they belong to.

This is not the case with an unload created by the BMC utilities; the null indicator does come after the unloaded column. It may be different with the native IBM unload, but I'd like to see evidence that that's so.
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 685
Location: Earth

PostPosted: Thu Aug 15, 2013 9:08 pm    Post subject:
Reply with quote

Here is the link showing Null indicator preceeds the field for IBM UNLOAD.
DSNTIAUL however, is a different animal.
I couldn't find a manual link however, I tested DSNTIAUL unload with nullable column. Turns out null indicator comes after the field in DSNTIAUL but, "?" character (x'6F') follows the field if the field is null.
If the field is not null, then x'00'.

SYSPUNCH load control card indicates the same.
Code:
 DATE EXTERNAL(            10)     
      NULLIF(      67)='?',       


DCLGEN is not a good indicator of expected unload record length.
Again, SYSPUNCH should provide true picture of unload dataset structure to the OP.
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Fri Aug 16, 2013 6:49 am    Post subject:
Reply with quote

thank you all.
I checked the SYSPUNCH and viewed the SYSIBM.SYSCOLUMNS data of the table , and I found that there is a NULL indicator column there.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Sat Aug 17, 2013 12:34 am    Post subject:
Reply with quote

agkshirsagar wrote:
Here is the link showing Null indicator preceeds the field for IBM UNLOAD.
DSNTIAUL however, is a different animal.
I couldn't find a manual link however, I tested DSNTIAUL unload with nullable column. Turns out null indicator comes after the field in DSNTIAUL but, "?" character (x'6F') follows the field if the field is null.
If the field is not null, then x'00'.

SYSPUNCH load control card indicates the same.
Code:
 DATE EXTERNAL(            10)     
      NULLIF(      67)='?',       


DCLGEN is not a good indicator of expected unload record length.
Again, SYSPUNCH should provide true picture of unload dataset structure to the OP.

Exactly right. This incompatibility between DSNTIAUL and the UNLOAD utility is a barrier that prevents some shops from using UNLOAD more widely, even though it is generally more efficient.

Among its many virtues, UNLOAD has the option to produce the output file in a CSV format simply by specifying "DELIMITED" in the UNLOAD statement.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Joinkeys operation when key1 and key2... Susanta DFSORT/ICETOOL 5 Wed Nov 02, 2016 5:35 pm
No new posts Best IMS DB Unload Utility jjabez10 IMS DB/DC 12 Thu Sep 22, 2016 11:15 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Format to pipe delimited with variabl... pshongal SYNCSORT 6 Wed Sep 14, 2016 2:48 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us