|
View previous topic :: View next topic
|
| Author |
Message |
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
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 |
|
 |
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
| 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 |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| Are any of the columns NULLable? If so, then DSNTIAUL has to reserve space for a null indicator. |
|
| Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
| 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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| 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 |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
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 |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
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 |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| 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 |
|
|
|
|
