IBM Mainframe Forum

by **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:

Select all

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 *

Select all

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:

Select all

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!!!

by **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.

by **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:

Select all

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:

Select all

-------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
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:

Select all

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:

Select all

-------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
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!!!

by **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. . .

by **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.

by **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. . .

by **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:

Select all

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

Then, query the content of the table after the load, shows the following:

Select all

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!

by **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.

by **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

by **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!

IBM Mainframe Forum

Help: cannot load smallint data correctly

Help: cannot load smallint data correctly

Re: Help: cannot load smallint data correctly

Re: Help: cannot load smallint data correctly

Re: Help: cannot load smallint data correctly

Re: Help: cannot load smallint data correctly

Re: Help: cannot load smallint data correctly

Re: Help: cannot load smallint data correctly

Re: Help: cannot load smallint data correctly

Re: Help: cannot load smallint data correctly

Re: Help: cannot load smallint data correctly