IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Loading data to table gives wrong for smallint type column.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Thu Jul 13, 2017 2:11 pm
Reply with quote

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
Back to top
View user's profile Send private message
prino

Senior Member


Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania

PostPosted: Thu Jul 13, 2017 2:54 pm
Reply with quote

Raghu navaikulam wrote:
Any clue to overcome this issue is expected.

Check the columns on your load statement!
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Thu Jul 13, 2017 5:08 pm
Reply with quote

Hi Prino,

Thanks for your quick replay.

Regards
Raghu
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Jul 13, 2017 8:32 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Thu Jul 13, 2017 8:35 pm
Reply with quote

-3594 is X'F1F6' as a two-byte hexadecimal value.
Back to top
View user's profile Send private message
prino

Senior Member


Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania

PostPosted: Thu Jul 13, 2017 9:04 pm
Reply with quote

Robert Sample wrote:
-3594 is X'F1F6' as a two-byte hexadecimal value.

Why do you have to spoil things? icon_wink.gif
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Thu Jul 13, 2017 9:21 pm
Reply with quote

icon_smile.gif It's not COMPLETELY spoiled -- I just gave a hint; it's up to the TS to interpret the hint correctly.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Thu Jul 13, 2017 9:55 pm
Reply with quote

Raghu navaikulam wrote:
Any clue to overcome this issue is expected.

Regards
Raghu

At this forum, people are making fun of you, and just laughing at your misunderstanding of IT fundamentals, don't you feel it?
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Thu Jul 13, 2017 10:26 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Mon Jul 17, 2017 12:18 pm
Reply with quote

@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
Back to top
View user's profile Send private message
prino

Senior Member


Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania

PostPosted: Mon Jul 17, 2017 12:47 pm
Reply with quote

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...
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Mon Jul 17, 2017 3:01 pm
Reply with quote

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 :
Quote:
DSNU3333I 198 14:36:06.04 DSNUGPRS - THE DB2 UTILITIES SUITE FOR Z/OS HAS NOT BEEN ENABLED.


I am using this version from the last week only. Before that I was using another Zos system which is having DB2 V8.1. I don't have such an issue with smallint in V8.1. That is why I posted the query.

Regards
Raghu
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Mon Jul 17, 2017 3:49 pm
Reply with quote

Have you read the part of the manual that describes changes from one release to another?
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Mon Jul 17, 2017 8:22 pm
Reply with quote

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
Quote:
Mainframe Skills: jcl, vsam, cobol, pl1, sql, cics
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Tue Jul 18, 2017 2:37 pm
Reply with quote

@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
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Jul 18, 2017 7:52 pm
Reply with quote

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!!
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Jul 18, 2017 8:29 pm
Reply with quote

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
Code:
"Column Name" POSITION(xx) INTEGER EXTERNAL(2)
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Fri Jul 21, 2017 2:14 pm
Reply with quote

@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
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Fri Jul 21, 2017 2:27 pm
Reply with quote

@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
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Mon Jul 24, 2017 9:02 pm
Reply with quote

You got it!! thanks.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts How to load to DB2 with column level ... DB2 6
No new posts SCOPE PENDING option -check data DB2 2
Search our Forums:

Back to Top