DBSU DATALOAD problem



IBM's flagship relational database management system

DBSU DATALOAD problem

Postby princehaasil » Wed Dec 15, 2010 5:52 pm

hi every one,
i am facing a problem while loading data from sequential file reside in TAPE into the DB2 Table using DBSU DATALOAD Command.

the problem is, input data is of type binary and target field is SQL INTEGER and it can't be convert binary field into integer.

DBSU book tells this utility automatically convert binary field into integer.

my running environment is ;

DB2 Server for VSE/VM version 7.5 under the Operating System VSE 4.2

and the error i got

[color=#4000FF]ARI0852I DATALOAD TABLE processing started.
ARI0868I DNAME=SYS004 RECFM=F RECSZ=300 BLKSIZE=300
ARI0866E Input record data field error:
Column = JOB_NUMB Input Record Count = 1
(000001): F1F061F3F061F1F00154641F0154654F40404040404040404040404040404040C4C2F2
(000056): 52000000000003002500DD00BD00000000D7E6D9E5E2C5F340D7E6D9E5E2C5F340C1D4
(000111): D6D3C440E2E3C1D9E3E4C6F810D300000F98C1D9C9E2D8D3C4E200183000000001F700
(000166): 0C0000000E0B01000000170B050000219C0FEE0000007D204040404040404040000000
(000221): 0000000000000000000000000000000000000000000000000000000000000000000000
(000276): 00000000000000000000000000000000000000000000000000
[/color]

please help me on this .
princehaasil
 
Posts: 11
Joined: Mon Jan 04, 2010 5:51 pm
Has thanked: 1 time
Been thanked: 0 time

Re: DBSU DATALOAD problem

Postby GuyC » Wed Dec 15, 2010 7:54 pm

what do you mean : "input data is of type binary "
F1F061F3F061F1F00154641F0154654F40404040404040404040404040404040C4C252 doesn't look binary to me . more like
char(10) : "10/30/10"
dec(7,0) : 0154641
dec(7,0) : 0154654
char(18) : " DB"
...
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: DBSU DATALOAD problem

Postby dick scherrer » Thu Dec 16, 2010 1:56 am

Hello,

What is the COBOL file layout/definition (copybook?) for this file?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: DBSU DATALOAD problem

Postby princehaasil » Mon Dec 20, 2010 1:30 pm

GuyC wrote:what do you mean : "input data is of type binary "
F1F061F3F061F1F00154641F0154654F40404040404040404040404040404040C4C252 doesn't look binary to me . more like
char(10) : "10/30/10"
dec(7,0) : 0154641
dec(7,0) : 0154654
char(18) : " DB"
...


Hi GuyC thanx for your response. you ask me what do i mean to say input data type is binary. may be i am not clarify my problem in your mind. actually my input data is mixture of char , packed decimal , binary and alphanumeric data types.
for this i am pasting here my input record format;

01 ACCOUNT-REC.
02 ACDATE1 PIC X(8).
02 ACSTRT1 PIC S9(7) COMP-3.
02 ACSTOP1 PIC S9(7) COMP-3.
02 PCUSER PIC X(16).
02 JNAME PIC X(8).
02 JNUMB PIC S9(4) COMP.
02 RECTYPE PIC X.
02 CANCCODE PIC XX.
02 CANCCODE-R REDEFINES CANCCODE PIC 9(3) COMP-3.
02 PRNTRADD PIC XXX.
02 FILLER PIC X(10).
02 PRNTDCOP PIC 9(2) COMP.
02 FILLER PIC X(8).
02 PRNTDPAG PIC 9(4) COMP.
02 FILLER PIC X(16).
02 EXCNODE PIC X(2).
02 EXUSER PIC X(8).
02 FILLER PIC X(10).
02 LSTNODE PIC X(2).
02 FILLER PIC X(12).
02 PARTID PIC X(2).
02 FILLER PIC X(2).
02 JDURAT PIC 9(8) COMP.
02 JPHASE PIC X(8).
02 FILLER PIC X(4).
02 JCPUTM PIC 9(8) COMP.
02 JOVHTM PIC 9(8) COMP.
02 JWAIT PIC 9(8) COMP.
02 FILLER PIC X(148).


data type COMP-3 is used for PAcked Decimal and COMP is used for Binary Data. the problem is neither packed decimal nor binary data loaded into the DB2 table, getting the same error for both these data types while loading data.

data type defined in Table for these input data types are DECIMAL for COMP-3 data and INTEGER for COMP data.

hope this clarify my problem in your mind. once again thanx for your reply and waiting for your reply on this post.
princehaasil
 
Posts: 11
Joined: Mon Jan 04, 2010 5:51 pm
Has thanked: 1 time
Been thanked: 0 time

Re: DBSU DATALOAD problem

Postby GuyC » Mon Dec 20, 2010 2:45 pm

as far as i can see , you haven"t posted the completed 1st record
"line (000001): " only shows the first 35 bytes. I guess you forgot to press PF11
level                             Pos     Hex                                  Value
02 ACDATE1 PIC X(8).               01     F1F061F3F061F1F0                     10/30/10
02 ACSTRT1 PIC S9(7) COMP-3.       09     00154641F                            154.641
02 ACSTOP1 PIC S9(7) COMP-3.       13     0154654F                             154.645
02 PCUSER PIC X(16).               17     40404040404040404040404040404040     spaces             
02 JNAME PIC X(8).                 33     C4C2F2??????                         DB2?????



"line (000056): " doesn't resemble your cobol layout.

I'm not so sure about "PIC 9(2) COMP" If I remember correctly the minimum binary number supported in cobol is an halfword, 2 bytes so pic 9(2) comp takes up as much as pic s9(4) comp.

If you continue mapping the output to your cobol layout like i did in the code-box , you might figure out what's wrong
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: DBSU DATALOAD problem

Postby dick scherrer » Tue Dec 21, 2010 3:38 am

Hello,

The following is from a compilation of a few COMP fields:

     01  SOME-COMP-STUFF.                                   
         05 ACOMP1         PIC 9(1)  COMP VALUE ZEROS.     
                                                           
         05 ACOMP2         PIC 9(2)  COMP VALUE ZEROS.     
                                                           
         05 ACOMP4         PIC 9(4)  COMP VALUE ZEROS.     
                                                           
         05 ACOMP5         PIC 9(5)  COMP VALUE ZEROS.     
                                                           
         05 ACOMP8         PIC 9(8)  COMP VALUE ZEROS.     
                                                           
         05 ACOMP9         PIC 9(9)  COMP VALUE ZEROS.     
                                                           
         05 ACOMP13        PIC 9(13) COMP VALUE ZEROS.

     01  SOME-OTHER-STUFF.     


Hierarchy and                                    Base       Hex-Displacement
Data Name                                        Locator    Blk   

1  SOME-COMP-STUFF . . . . . . . . . . . . . . . BLW=00000  050 DS 0CL26 
  2  ACOMP1. . . . . . . . . . . . . . . . . . . BLW=00000  050 DS 2C
  2  ACOMP2. . . . . . . . . . . . . . . . . . . BLW=00000  052 DS 2C
  2  ACOMP4. . . . . . . . . . . . . . . . . . . BLW=00000  054 DS 2C
  2  ACOMP5. . . . . . . . . . . . . . . . . . . BLW=00000  056 DS 4C
  2  ACOMP8. . . . . . . . . . . . . . . . . . . BLW=00000  05A DS 4C
  2  ACOMP9. . . . . . . . . . . . . . . . . . . BLW=00000  05E DS 4C
  2  ACOMP13 . . . . . . . . . . . . . . . . . . BLW=00000  062 DS 8C
1  SOME-OTHER-STUFF. . . . . . . . . . . . . . . BLW=00000  070


Note the lengths determined by the compiler.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: DBSU DATALOAD problem

Postby princehaasil » Tue Dec 21, 2010 12:29 pm

hi there,
both of you are correct but i think we deviate from our problem. problem is not in my COBOL program, my problem is which data type is used for these,
02 ACSTRT1 PIC S9(7) COMP-3.
02 ACSTOP1 PIC S9(7) COMP-3.
02 JNUMB PIC S9(4) COMP.
02 PRNTDCOP PIC 9(2) COMP.
02 PRNTDPAG PIC 9(4) COMP.
02 JDURAT PIC 9(8) COMP.
02 JCPUTM PIC 9(8) COMP.
02 JOVHTM PIC 9(8) COMP.
02 JWAIT PIC 9(8) COMP.


in my table, so that DATALOAD utility correctly load my input data.
Currently i am using these data types for above mentioned fields;

CNAME COLTYPE LENGTH SYSLENGTH
------------------ ---------- --------- -------------
JOB_STRTTM DECIMAL 7 1792
JOB_ENDTM DECIMAL 7 1792
JOB_NUMB INTEGER 4
PRNTD_COP SMALLINT 2
PRNTD_PAG FLOAT 8
JOB_DURAT INTEGER 4
JOB_CPUTM INTEGER 4
JOB_OVHTM INTEGER 4
JOB_WAITM INTEGER 4


I tried almost every type for these fields but no use except the Char. if use then loaded but the garbage.

and GuyC you told me to map the data manually to check, I did and succeded but problem remains there how would i load it using DATALOAD.

Dick, i think the way you thought is not right, it does not depend upon compiler. GUyc is right about Binary numbers are saved in Halfword, 2bytes. based on this, binary lenght (1-4) are saved in 2 bytes, BL(5-8) are in 4 bytes and so on...

Please Suggest me which data type I use for these fields. so i am able to load data into the table successfully.
princehaasil
 
Posts: 11
Joined: Mon Jan 04, 2010 5:51 pm
Has thanked: 1 time
Been thanked: 0 time

Re: DBSU DATALOAD problem

Postby dick scherrer » Wed Dec 22, 2010 12:27 am

Hello,

Dick, i think the way you thought is not right, it does not depend upon compiler.
Where did i say this depends on the compiler? What i posted is that the length is determined by the compiler (you, the coder, do not specify the number of bytes to allocate - the compiler calcultes this. My post was to show the generated lengths for a few different length binary fields.

I tried almost every type for these fields but no use except the Char. if use then loaded but the garbage.
It is just as important to specify the correct length in addition to specifying the correct type. . .

It will be much more useful if you discontinue posting rainbow entries and simply use the Code tag (notice the code i posted using the Code tag is properly aligned as quite readable). There is also a Preview so you can see your post as it will appear to the forum. When your post appears as you wish, Submit.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: DBSU DATALOAD problem

Postby GuyC » Wed Dec 22, 2010 2:18 pm

smallint for all 9(4) comp,9(2) comp : length = 2bytes
integer for all 9(8) comp : length = 4bytes
decimal (7,0) for 9(7) comp-3 : length = 4bytes
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: DBSU DATALOAD problem

Postby princehaasil » Fri Dec 24, 2010 4:37 pm

hello,
now i m posting here my complete information, input record format , my current table fields and its type and error log. please review this and tell me whats wrong in this. thanks in advance.

INPUT RECORD FORMAT
        01   ACCOUNT-REC.                                                 
             02  ACDATE1         PIC    X(8).                                       
             02  ACSTRT1         PIC    S9(7) COMP-3.                               
             02  ACSTOP1         PIC    S9(7) COMP-3.                               
             02  PCUSER           PIC    X(16).                                       
             02  JNAME            PIC     X(8).                                         
             02  JNUMB            PIC     S9(4) COMP.                                   
             02  RECTYPE         PIC     X.                                           
             02  CANCCODE      PIC     XX.                                         
             02  PRNTRADD      PIC     XXX.                                       
             02  FILLER        PIC     X(10).                                       
             02  PRNTDCOP      PIC     9(2) COMP.                                 
             02  FILLER            PIC     X(8).                                       
             02  PRNTDPAG      PIC     9(4) COMP.                                 
             02  FILLER            PIC     X(16).                                       
             02  EXCNODE        PIC     X(2).                                       
             02  EXUSER          PIC     X(8).                                         
             02  FILLER           PIC     X(10).                                       
             02  LSTNODE        PIC     X(2).                                       
             02  FILLER           PIC     X(12).                                       
             02  PARTID          PIC     X(2).                                       
             02  FILLER           PIC     X(2).                                       
             02  JDURAT         PIC      9(8) COMP.                                   
             02  JPHASE          PIC      X(8).                                         
             02  FILLER           PIC      X(4).                                         
             02  JCPUTM         PIC      9(8) COMP.                                   
             02  JOVHTM         PIC     9(8) COMP.                                   
             02  JWAIT           PIC      9(8) COMP.                                   
             02  FILLER          PIC      X(148).                                     


TABLE FIELDS AND DATA TYPE
 CNAME               COLTYPE         LENGTH         SYSLENGTH                                                   
 ------------------  -----------         ----------         --------------
 CANC_CODE         CHAR                   4                         4
 EXUSER         CHAR                   8                         8
 JOB_CPUTM         INTEGER                                          4
 JOB_DATE         CHAR                   8                         8
 JOB_DURAT         INTEGER                                         4
 JOB_ENDTM         DECIMAL            (7 , 0)                     1792
 JOB_NAME         CHAR                 8                           8
 JOB_NUMB         SMALLINT                                       2
 JOB_OVHTM         INTEGER                                         4
 JOB_PHASE         CHAR               8                            8
 JOB_STRTTM         DECIMAL          (7 , 0)                       1792
 MACHINE_ID         CHAR               2                            2
 PART_ID         CHAR               2                            2
 PRINTER_ADD         CHAR               3                            3
 PRNTD_COP         SMALLINT                                      2
 PRNTD_PAG         SMALLINT                                      2
 RECTYPE         CHAR               1                            1


ERROR I GOT

------> DATALOAD TABLE (JOB_ACCNTNG)                                           
------> JOB_DATE    1-8                                                         
------> JOB_STRTTM  9-12                                                       
------> JOB_ENDTM  13-16                                                       
------> JOB_NAME   33-40                                                       
------> RECTYPE    43                                                           
------> CANC_CODE  44-45                                                       
------> MACHINE_ID 87-88                                                       
------> EXUSER     89-96                                                       
------> PART_ID   121-122                                                       
------> INFILE (SYS004  BLKSZ(300) )
                                           
ARI0852I DATALOAD TABLE processing started.                                     

ARI0868I DNAME=SYS004 RECFM=F RECSZ=300 BLKSIZE=300                             

ARI0866E Input record data field error:                                         
         Column = JOB_STRTTM Input Record Count = 1 
                           
(000001): F1F061F3F061F1F00154641F0154654F40404040404040404040404040404040C4C2F2C3D6D3C44017B9C510800000000000F8F9000000
(000056): 52000000000003002500DD00BD00000000D7E6D9E5E2C5F340D7E6D9E5E2C5F340C1D4C5D940404040C4C2F2C3D6D3C4404040404040C3
(000111): D6D3C440E2E3C1D9E3E4C6F810D300000F98C1D9C9E2D8D3C4E200183000000001F70000009F000001130B00000000680FEC0000000A03
(000166): 0C0000000E0B01000000170B050000219C0FEE0000007D2040404040404040400000000300000000000000000000000000000000000000
(000221): 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
(000276): 00000000000000000000000000000000000000000000000000                   
                                                                                                   
ARI0857E DATALOAD processing unsuccessful.                                     
ARI8998I ...Begin ROLLBACK processing.                                         
ARI0811I ...ROLLBACK of any database changes successful.                       
ARI0813I ...Suspend command execution:                                         
         AUTOCOMMIT = OFF ERRORMODE = ON                                       
------> COMMIT WORK;                                                           
ARI0819I ...Command not processed: ERRORMODE = ON                               
ARI0802I End of command file input.                                             
ARI0807E ...Errors occurred during command processing.                         
ARI0808I DBS processing completed: 24/12/10 15:11:13.                           
1S55I  LAST RETURN CODE WAS 0008                                               
EOJ DATALOD   MAX.RETURN CODE=0008                                  DATE 24/12/2010   



and more thing JNUMB FIELD in my input field contains data from (0000-FFFF) in decimal(0-65535). it beyond the range of SMALLINT type. thats y i chose INT as my first choice for JOB_NUMB field in my table. but bcoz of your replies i recreated my table as according to the last reply by GuyC.

but no use. m still there where we started this thread.
any suggestion wud b appreciable.
princehaasil
 
Posts: 11
Joined: Mon Jan 04, 2010 5:51 pm
Has thanked: 1 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post