|
View previous topic :: View next topic
|
| Author |
Message |
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
I found something strange.
IF I use the following step to unload data from a table,
| Code: |
//*------------------------------------------------------------------*
//RECOVER EXEC DSNUPROC,SYSTEM=SUN,UID='EV6954RV',UTPROC=''
//DSNUPROC.STEPLIB DD DSN=SUN.SDSNLOAD,DISP=SHR
//DSNUPROC.SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,(30,30),RLSE)
//DSNUPROC.SYSPUNCH DD DSN=EV6954.SUNUNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// RECFM=FB,LRECL=120,BLKSIZE=1200
//DSNUPROC.SYSREC00 DD DSN=EV6954.DS.TM11.UNLOAD,DISP=(,CATLG),
// UNIT=SYSDA,
// SPACE=(CYL,(100,100),RLSE)
//DSNUPROC.SYSIN DD *
UNLOAD DATA FROM TABLE LJ84U1.LJ84TM11
PUNCHDDN SYSPUNCH UNLDDN SYSREC00 UNICODE DELIMITED
/* |
THE punch data is as follows.
| Code: |
EV6954.SUNUNLD.SYSPUNCH
ド ===>
***************************** データの始め ******
LOAD DATA INDDN SYSREC00 LOG NO RESUME YES
UNICODE CCSID(01208,01208,01208)
FORMAT DELIMITED
SORTKEYS 36630
INTO TABLE "LJ84U1"."LJ84TM11"
( "RCUXA"
POSITION(*) CHAR MIXED(018)
, "CCUGP"
POSITION(*) CHAR MIXED(003)
, "RESXA"
POSITION(*) CHAR MIXED(018)
, "NCUXB"
POSITION(*) CHAR MIXED(066)
, "UNCUX01"
POSITION(*) CHAR MIXED(186)
, "UNCUX02"
POSITION(*) CHAR MIXED(186)
... ... |
But if I load the data and the punch DS back into the original table, the following error message occurred, and the job abend.
| Code: |
DSNURPIB - NUMBER OF TASKS CONSTRAINED BY SWA=BELOW
@SUN DSNURCON - INPUT FIELD 'RCUXA' INVALID FOR 'LJ84U1.LJ84TM11', ERROR CODE '19'
@SUN DSNURWBF - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED=1
DSNUGBAC - UTILITY DATA BASE SERVICES MEMORY EXECUTION ABENDED, REASON=X'00E40323'
******************************* BOTTOM OF DATA ******************************** |
According to DB2 manuals, http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.msgs/dsnu334i.htm
ERROR CODE '19' indicates "Input is too long for the table column after a required CCSID conversion."
The data I used to load into the table was just the data generated when I unload from the table. I really do not understand why the above error would occur!
Another question, if I unload data from table using the following JOB step,
| Code: |
//*------------------------------------------------------------------*
//RECOVER EXEC DSNUPROC,SYSTEM=SUN,UID='EV6954RV',UTPROC=''
//DSNUPROC.STEPLIB DD DSN=SUN.SDSNLOAD,DISP=SHR
//DSNUPROC.SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,(30,30),RLSE)
//DSNUPROC.SYSPUNCH DD DSN=EV6954.SUNUNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// RECFM=FB,LRECL=120,BLKSIZE=1200
//DSNUPROC.SYSREC00 DD DSN=EV6954.DS.TM11.UNLOAD,DISP=(,CATLG),
// UNIT=SYSDA,
// SPACE=(CYL,(100,100),RLSE)
//DSNUPROC.SYSIN DD *
UNLOAD DATA FROM TABLE LJ84U1.LJ84TM11
PUNCHDDN SYSPUNCH UNLDDN SYSREC00
/*
// |
The PUNCH data was generated like follow.
| Code: |
表示 EV6954.SUNUNLD.SYSPUNCH.@
コマンド ===>
********************************* データの始め *******
LOAD DATA INDDN SYSREC00 LOG NO RESUME NO REPLACE
EBCDIC CCSID(01027,05035,04396)
SORTKEYS 36630
INTO TABLE "LJ84U1"."LJ84TM11"
WHEN(00001:00002) = X'0025'
( "RCUXA"
POSITION( 00003:00008) CHAR MIXED(006)
, "CCUGP"
POSITION( 00009:00009) CHAR MIXED(001)
, "RESXA"
POSITION( 00010:00015) CHAR MIXED(006)
, "NCUXB"
POSITION( 00016:00037) CHAR MIXED(022)
, "UNCUX01"
...... |
I do not understand why ' WHEN(00001:00002) = X'0025' 'appeared in the D/S. Can you explain what this stands for? How is its usage?
Thanks in advance |
|
| Back to top |
|
 |
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
well, as we all know, basically there are two ways to unload data from a table/tablespace.
1. using the utility I mentioned above;
2. using the sample program named DSNTIAUL provided by IBM.
If I want to unload data with a specific CCSID code, I'm afraid that the 2nd method cannot do this.
This is the reason why I choose the 1st method to unload data.
Appreciate your help very much! |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10886
Location: italy
|
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| Thank you very much for your kind reply first. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10886
Location: italy
|
|
|
|
follow on,
it would be interesting to define a simple table with a few fields in both ways,
and run a dclgen to see what the two declares look like
it should take away all the doubts about the length error  |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
continue to ask.
As I mentioned in the first floor, I still have no idea about the second question.
| Quote: |
| I do not understand why ' WHEN(00001:00002) = X'0025' 'appeared in the D/S. Can you explain what this stands for? How is its usage? |
|
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
Can anybody please help to answer my question?
what does the following statement in the SYSPUNCH file stand for?
| Code: |
| WHEN(00001:00002) = X'0025' |
|
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| I tried to find the answer from the specification of UNLOAD utility, but eventually I got nothing that can help. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10886
Location: italy
|
|
|
|
| Code: |
WHEN(00001:00002) = X'0025'
( "RCUXA"
POSITION( 00003:00008) CHAR MIXED(006)
, "CCUGP"
POSITION( 00009:00009) CHAR MIXED(001)
, "RESXA"
POSITION( 00010:00015) CHAR MIXED(006)
, "NCUXB"
POSITION( 00016:00037) CHAR MIXED(022)
, "UNCUX01"
...... |
in the quoted fragment what is after the ...... ??
incomplete info will not hlp anybody |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
HELLO, enrico,
Thanks for your reply first.
the code" ...... " stands for the rest field name of the table. The name format is just the same as before-listed like below
| Code: |
, "CCUGP"
POSITION( 00009:00009) CHAR MIXED(001)
, "RESXA"
POSITION( 00010:00015) CHAR MIXED(006)
, "NCUXB"
POSITION( 00016:00037) CHAR MIXED(022) |
After the name & position list of the fields, there is nothing left.
So, I thought it was not necessary to put all the content of the dataset here.
I should have stated more specifically on this. Sorry for that and looking forward to your reply! |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10886
Location: italy
|
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
Maybe I did not state myself clearly.
I know the usage of WHEN clasue.
What I want to know is , why X'0025' will be set in the first two columns??
From the unload D/S, we can see , the first two columns are all set to X'0025' .
Does this have some special meaning?
| Code: |
----+----1----+----2----+----3
----+----F----+----F----+----F
----+----1----+----2----+----3
-----------------------------
.%A01820 A28289
024333332222222222222224333332
051018200000000000000001282890
-----------------------------
.%A01971 A28289
024333332222222222222224333332
051019710000000000000001282890
-----------------------------
.%A03726 A01294
024333332222222222222224333332
051037260000000000000001012940
-----------------------------
.%A03758 A01335
024333332222222222222224333332
051037580000000000000001013350
-----------------------------
|
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
It is the length of the data (x'25' = decimal 37)? |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| dick scherrer wrote: |
Hello,
It is the length of the data (x'25' = decimal 37)? |
You mean record length?
I think not. Because the total record length is 486bytes. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
, "NCUXB"
POSITION( 00016:00037) CHAR MIXED(022) |
That kinda jumped out. . .  |
|
| Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka
|
|
|
|
That WHEN thing is useful if you are unloading multiple tables in a single tablespace to a single unload file. So while loading it loads appropriate tables rows into right table.
The number in WHEN is the hex of the OBID of the table. |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| wanderer wrote: |
That WHEN thing is useful if you are unloading multiple tables in a single tablespace to a single unload file. So while loading it loads appropriate tables rows into right table.
The number in WHEN is the hex of the OBID of the table. |
Yes! Exactly!
I have confirmed it from SYSIBM.SYSTABLES, and it is actually the OBID of the table!
Thank you very much!
But would you please tell me how do you know this? I cannot find a clue from the redbooks and manuals. I'll appreciate it very much if you can tell me the method! |
|
| Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka
|
|
|
|
| dejunzhu wrote: |
But would you please tell me how do you know this? I cannot find a clue from the redbooks and manuals. I'll appreciate it very much if you can tell me the method! |
I am not sure what all the manuals you have checked. Is Utility Guide one of them? In Unload section, it clearly describes HEADER OBID option. |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
Yes , I find it.
I'm sorry for my carelessness. |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| wanderer wrote: |
That WHEN thing is useful if you are unloading multiple tables in a single tablespace to a single unload file. So while loading it loads appropriate tables rows into right table.
The number in WHEN is the hex of the OBID of the table. |
Now I want to leave the position of HEADER blank and let the data unloaded from table starts from column 1, is this feasible?
I looked for the UNLOAD utility and I cannot find any parameter can do this.
Even if I specify "HEADER NONE" in the UNLOAD control statement, the field still exist and the data starts from column 6.
How can I do to cut away HEADER field? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
One way is to use a bit of your own code. . . |
|
| Back to top |
|
|
|
|
