Loading data to table gives wrong for smallint type column.

Raghu navaikulam · Posted: Thu Jul 13, 2017 2:11 pm

Hi all,

While using LOAD utility, I am getting negative value in a smallint column.
The data I am giving to the smallint column 18, but after load, a select statement against the table is showing -3592. For the value 16, I am getting -3594. Other column types are showing the data perfectly. What is the possible reason and what will be the remedy? I am using a PS file as the SYSREC dataset in the JCL. I tried with
POSITION(START:END) SMALLINT and POSITION(*) SMALLINT.
In both cases the result is the same.
I am using DB2 V10.

Any clue to overcome this issue is expected.

Regards
Raghu

prino · Posted: Thu Jul 13, 2017 2:54 pm

Raghu navaikulam · Posted: Thu Jul 13, 2017 5:08 pm

Hi Prino,

Thanks for your quick replay.

Regards
Raghu

Rohit Umarjikar · Posted: Thu Jul 13, 2017 8:32 pm

Please verify syspunch with DB2 DDL definitions and make sure they are identical and ask DBA for help, if you still struggles infact they should be your first poc.

Robert Sample · Posted: Thu Jul 13, 2017 8:35 pm

-3594 is X'F1F6' as a two-byte hexadecimal value.

prino · Posted: Thu Jul 13, 2017 9:04 pm

Robert Sample · Posted: Thu Jul 13, 2017 9:21 pm

It's not COMPLETELY spoiled -- I just gave a hint; it's up to the TS to interpret the hint correctly.

sergeyken · Posted: Thu Jul 13, 2017 9:55 pm

Robert Sample · Posted: Thu Jul 13, 2017 10:26 pm

We are NOT making fun of the TS. However, this is supposedly a forum for experts, and the TS joined almost 9 years ago and hence presumably has some experience. Someone not understanding data formats and transformation after 9 years in the field is definitely NOT a laughing matter.

Raghu navaikulam · Posted: Mon Jul 17, 2017 12:18 pm

@Robert

Thank you Robert for your valuable information. Yes -3594 is the conversion value of 16, i.e. X'F1F6'. And I have given 'AB' for smallint value for testing. It is also accepted by the LOAD utility and given a value of -15934. That is X'FFFFC1C2'.
The problem still remains the same. What is the remedy? I checked the IBM Manual for "Utility Guide and Reference for Version 10". In that there is no special mention about the SMALLINT type.

@sergeyken

Till now I got only help from this forum. People like you sometime make fun of other because you feel that you are wise but others think "otherwise".
So don't try to fool anyone whether he/she is new to DB2 or an experience in DB2.

Very sorry for writing these word.

Regards
Raghunathan

prino · Posted: Mon Jul 17, 2017 12:47 pm

You've been on this forum for 9 years, that seems to imply that you've been working in IT for at least as long, yet you still seem to be unable to RTFM, and figure out what's wrong...

Raghu navaikulam · Posted: Mon Jul 17, 2017 3:01 pm

Hi Robert

I have rectified the issue in another way. Instead of using smallint, I used integer external by providing 4 bytes (2 blanks and 2 digits) in the input file. The data is loaded as expected. When I changed to smallint and again run with 2 bytes of data, records are loaded but the data in the tables shows with negative values as I mentioned in my first post.
In the spool, I got a an informational message :

Nic Clouston · Posted: Mon Jul 17, 2017 3:49 pm

Have you read the part of the manual that describes changes from one release to another?

Rohit Umarjikar · Posted: Mon Jul 17, 2017 8:22 pm

Why are you afraid of contacting your DBA when you advised already instead of arguing with someone who is helping you?
And your mainframes skill don't look you like you are a DBA itself

Raghu navaikulam · Posted: Tue Jul 18, 2017 2:37 pm

@Nic Clouston,

I have read the manual for V10. In the load utility, the control cards showing no change for smallint. No additional parameters mentioned in the manual "DB2 10 for z/OS Utility Guide and Reference" page no. 273. So I did not specified anything along with smallint datatype.

@Rohit Umarjikar.
I am not a DBA. I am working as a trainer now. It is not compulsory for me to contact DBA for this case since LOAD is not included as part of training.

And I am not forcing to help me Rohit. I am not using the word "ASAP".
If somebody know the issue, have free time and a helping mind then only I am expecting a reply.
After all this is a voluntary service.

Thanks Rohit

Rohit Umarjikar · Posted: Tue Jul 18, 2017 7:52 pm

It is not about forcing anybody but it is about to do right thing, The things which owns by the DBA should be done by them and as a Developer we should not waste our time which DBA's has it on top of their head.
DBA's are there to take care of all DB2 utilities issues otherwise they Wouldn't be there at first place.
Even if LOAD is not part of your training, the SELECT of that table must be of it, hence no harm dropping them an email so if they respond well and good else standby.

All the best!!

Rohit Umarjikar · Posted: Tue Jul 18, 2017 8:29 pm

I am sure any of the below case is the cause of the problem, give a try.

1.Your load dataset may be incorrect( which you could have shown us in your first post itself), How did you prepare your load data set by typing? If so then I suggest you to do this,
a.Insert one row into the table
b.Unload the table
c. Load it back with this unload data set with no change in Load control stmt.

2. Try

Raghu navaikulam · Posted: Fri Jul 21, 2017 2:14 pm

@Rohit,

Thank you for your suggestion.

The point 1 is a good thing where we are forcing DB2 Load utility to create a control card for us.

Second point I tried with SMALLINT EXTERNAL, but failed.
I tried with INTEGER EXTERNAL. It worked, but 4 bytes of data has to be given. So I extended my dataset by 2 bytes and executed successfully.
Now I am trying with "INTEGER EXTERNAL(2)". I did not tried because as per the DB2 utility manual it is not a supported syntax.

Anyway Thanks for your replay.

Regards
Raghu

Raghu navaikulam · Posted: Fri Jul 21, 2017 2:27 pm

@Rohit,

The second solution in your post worked successfully
"(INTEGER EXTERNAL(2)" without adding 2 bytes. It uses only 2 bytes of data and the result is OK now.

Thanks once again

Regards
Raghu

Rohit Umarjikar · Posted: Mon Jul 24, 2017 9:02 pm

You got it!! thanks.