Help: cannot load smallint data correctly



IBM's flagship relational database management system

Help: cannot load smallint data correctly

Postby JoyceJ » Thu Jul 22, 2010 10:48 pm

I do not know how to use load utility to load smallint data correctly.

Here is my db2 table ddl:

CREATE TABLE T1EMP                                                             
( EMPNO                CHAR(6)     NOT NULL,                                       
  FIRST_NAME        CHAR(20)    NOT NULL,                                       
  MIDDLE_INITIAL   CHAR(1),                                                     
  SEX                     CHAR(1),                                                     
  BIRTHDATE         DATE,                                                       
  PHONE                CHAR(4),                                                     
  HIRING_DATE      DATE,                                                       
  JOB                     CHAR(20),                                                   
  EDUCATION         SMALLINT    NOT NULL)                                       
PARTITION BY (EMPNO ASC)                                                       
  ( PARTITION 1 ENDING AT ('000150'),                                           
    PARTITION 2 ENDING AT ('000230'),                                           
    PARTITION 3 ENDING AT ('001000') )                                         
  IN TESTD.T2TSEMPL;   


This my flat file that is stored at mainframe system:

         1         2         3         4         5         6         7         8
1...5....0....5....0....5....0....5....0....5....0....5....0....5....0....5....0....5....0
000200                 DAVID  D                  M   06/27/1990  2200   02/12/2000                    DBA      10     
000210            WILLIAM  W                  M   09/12/1970  1234   09/11/1998      PROGRAMMER     10   
000220            JENNIFER  J                   F   10/23/1978   2345   02/15/1983      PROGRAMMER     10   

Here is part of my load JCL:

//DSNUPROC.SYSIN DD *
LOAD DATA INTO TABLE DB2TA.T1EMP                     
   (EMPNO                POSITION(1)  CHAR(6),             
    FIRST_NAME        POSITION(07) CHAR(20),             
    MIDDLE_INITIAL   POSITION(27) CHAR(1),             
    SEX                     POSITION(28) CHAR(1),             
    BIRTHDATE          POSITION(29) DATE EXTERNAL(10),   
    PHONE                POSITION(39) CHAR(4),             
    HIRING_DATE      POSITION(43) DATE EXTERNAL(10),   
    JOB                     POSITION(53) CHAR(20),             
    EDUCATION         POSITION(73) SMALLINT
)             


When I use load utility to load data from flat file to table T2TSEMPL, the column for EDUCATION shows as -3600, which is not correct. The rest of the columns shows correct data.

Then, I changed my flat file:

000200                 DAVID  D                  M   06/27/1990  2200   02/12/2000                    DBA      +10     
000210            WILLIAM  W                  M   09/12/1970  1234   09/11/1998      PROGRAMMER     +10   
000220            JENNIFER  J                   F   10/23/1978   2345   02/15/1983      PROGRAMMER     +10


The column for EDUCATION shows as 20209, and the rest of the columns shows correct data.

This is first time I use smallint. Please help me solve this problem. Thanks a lot!!!
JoyceJ
 
Posts: 4
Joined: Thu Jul 22, 2010 9:54 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Help: cannot load smallint data correctly

Postby dick scherrer » Thu Jul 22, 2010 11:56 pm

Hello and welcome to the forum,

Suggest you become familiar/comfortable with the "Code" tag and the Preview function.

Also when posting positional data, it is best to include a "ruler" (one has been placed at the top of your first set of data). You can copy this anywhere you need it.

Suggest you change the education values form 10 in all 3 cases to some other values and see what is loaded.

The difference between an int and a smallint is capacity.

Suggest you post the informatonal output from the load for review.
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: Help: cannot load smallint data correctly

Postby JoyceJ » Fri Jul 23, 2010 1:21 am

Re-type:

I do not know how to use load utility to load smallint data correctly.

Here is my db2 table ddl:
                                           
CREATE TABLE T1EMP                                                             
( EMPNO            CHAR(6)     NOT NULL,                                       
  FIRST_NAME       CHAR(20)    NOT NULL,                                       
  MIDDLE_INITIAL   CHAR(1),                                                     
  SEX              CHAR(1),                                                     
  BIRTHDATE        DATE,                                                       
  PHONE            CHAR(4),                                                     
  HIRING_DATE      DATE,                                                       
  JOB              CHAR(20),                                                   
  EDUCATION        SMALLINT    NOT NULL                                                                                           
   )                                     
PARTITION BY (EMPNO ASC)                                                       
  ( PARTITION 1 ENDING AT ('000150'),                                           
    PARTITION 2 ENDING AT ('000230'),                                           
    PARTITION 3 ENDING AT ('001000') )                                         
  IN TESTD.T2TSEMPL;                           


This my flat file that is stored at mainframe system:

-------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
000200                 DAVID  D               M    06/27/1990  2200   02/12/2000                    DBA      10     
000210               WILLIAM  W               M    09/12/1970  1234   09/11/1998             PROGRAMMER      10     
000220              JENNIFER  J               F    10/23/1978  2345   02/15/1983             PROGRAMMER      10   


Here is part of my load JCL:

LOAD DATA INTO TABLE DB2TA.T1EMP                                       
   (EMPNO          POSITION(1)  CHAR(6),                               
    FIRST_NAME     POSITION(07) CHAR(20),                               
    MIDDLE_INITIAL POSITION(27) CHAR(1),                               
    SEX            POSITION(28) CHAR(1),                                       
    BIRTHDATE      POSITION(29) DATE EXTERNAL(10),                             
    PHONE          POSITION(39) CHAR(4),                                       
    HIRING_DATE    POSITION(43) DATE EXTERNAL(10),                             
    JOB            POSITION(53) CHAR(20),                                       
    EDUCATION      POSITION(73) SMALLINT
   )               


When I use load utility to load data from flat file to table T2TSEMPL, the column for EDUCATION shows as -3600, which is not correct. The rest of the columns shows correct data.

Then, I changed my flat file:

-------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
000200                 DAVID  D               M    06/27/1990  2200   02/12/2000                    DBA      +10     
000210               WILLIAM  W               M    09/12/1970  1234   09/11/1998             PROGRAMMER      +10     
000220              JENNIFER  J               F    10/23/1978  2345   02/15/1983             PROGRAMMER      +10   



The column for EDUCATION shows as 20209, and the rest of the columns shows correct data.

This is first time I use smallint. Please help me solve this problem. Thanks a lot!!!
JoyceJ
 
Posts: 4
Joined: Thu Jul 22, 2010 9:54 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Help: cannot load smallint data correctly

Postby dick scherrer » Fri Jul 23, 2010 1:24 am

Hello,

Rather than re-posting your original info you need to go back and do as requested. . .

Your data posted is not in the positions your load statement specifies. The values should be changed so that they are not all ten (10) - if the loaded value stays the same, it is a clear indication that the load dis not use the values you intended.

There is no magic loading a small int that i'm aware of.

You might change the definition from smallint to int and run a test. . .
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: Help: cannot load smallint data correctly

Postby dick scherrer » Fri Jul 23, 2010 2:36 am

Hello,

Why did you start yet another topic and re-post your original request again. . . The extra topic has been deleted.

If you want the forum to help, provide the info requested, properly align the data in the positions the utility has been told to expect then, and/or run the experiment using int instead of smallint.
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: Help: cannot load smallint data correctly

Postby dick scherrer » Fri Jul 23, 2010 2:40 am

Actually, i believe your control statement should be neither smallint nor int.

The input data is not an integer. . .

+1 = 4EF1 which is 20209. . .
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: Help: cannot load smallint data correctly

Postby JoyceJ » Fri Jul 23, 2010 2:55 am

Do not quite understand this:

Actually, i believe your control statement should be neither smallint nor int.

The input data is not an integer. . .

+1 = 4EF1 which is 20209. . .


I can understand x(4EF1) = 20209, but how come +1 = 4EF1? Does it mean that my input data should be hexdecimal?

Would you please advise what the input data be like for smallint? Thanks a lot!

I just create a table, which only have one clumn and it is a smallint.

The input flat file:

+23
+11
+22
+01
+05
+89


Then, query the content of the table after the load, shows the following:
20210
20209
20210
20208
20208
20216


I do feel that I did not give the correct smallint input data. So, would you please shed light on this one? Thanks!
JoyceJ
 
Posts: 4
Joined: Thu Jul 22, 2010 9:54 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Help: cannot load smallint data correctly

Postby dick scherrer » Fri Jul 23, 2010 3:06 am

Hello,

A smallint is 2 bytes. When you used:
+23
+11
+22
+01
+05
+89
as input, the system saw only:
+2
+1
+2
+0
+0
+8
which is why the similarity in the results.

In order to specify smallint (or int) the value in the input would need to be in binary (hex).

Hex 4E is the plus (+) sign Hex F1 is a one (1).

I have no way to run a load here, but you might try using smallint external on the LOAD.
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: Help: cannot load smallint data correctly

Postby dick scherrer » Fri Jul 23, 2010 3:10 am

Just found this:
EDUCATION POSITION(73) SMALLINT
might become
EDUCATION POSITION(73) INTEGER EXTERNAL(2)

You need to make sure the position is the first digit, not the +-sign.

Fingers crossed ;)
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: Help: cannot load smallint data correctly

Postby JoyceJ » Fri Jul 23, 2010 3:28 am

This works! Thanks a lot!!! Really appreciate it!
EDUCATION POSITION(73) INTEGER EXTERNAL(2)



I also edited my input data for smallint column when "hex on", just load it as smallint data type, and then query the results and works as I thought it is should be. Thanks!
JoyceJ
 
Posts: 4
Joined: Thu Jul 22, 2010 9:54 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post