View previous topic :: View next topic
|
Author |
Message |
Raghu navaikulam
Active User
Joined: 27 Sep 2008 Posts: 193 Location: chennai
|
|
|
|
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 |
|
|
prino
Senior Member
Joined: 07 Feb 2009 Posts: 1314 Location: Vilnius, Lithuania
|
|
|
|
Raghu navaikulam wrote: |
Any clue to overcome this issue is expected. |
Check the columns on your load statement! |
|
Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008 Posts: 193 Location: chennai
|
|
|
|
Hi Prino,
Thanks for your quick replay.
Regards
Raghu |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3076 Location: NYC,USA
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8700 Location: Dubuque, Iowa, USA
|
|
|
|
-3594 is X'F1F6' as a two-byte hexadecimal value. |
|
Back to top |
|
|
prino
Senior Member
Joined: 07 Feb 2009 Posts: 1314 Location: Vilnius, Lithuania
|
|
|
|
Robert Sample wrote: |
-3594 is X'F1F6' as a two-byte hexadecimal value. |
Why do you have to spoil things? |
|
Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8700 Location: Dubuque, Iowa, USA
|
|
|
|
It's not COMPLETELY spoiled -- I just gave a hint; it's up to the TS to interpret the hint correctly. |
|
Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2140 Location: USA
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8700 Location: Dubuque, Iowa, USA
|
|
|
|
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 |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008 Posts: 193 Location: chennai
|
|
|
|
@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 |
|
|
prino
Senior Member
Joined: 07 Feb 2009 Posts: 1314 Location: Vilnius, Lithuania
|
|
|
|
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 |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008 Posts: 193 Location: chennai
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2454 Location: Hampshire, UK
|
|
|
|
Have you read the part of the manual that describes changes from one release to another? |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3076 Location: NYC,USA
|
|
|
|
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 |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008 Posts: 193 Location: chennai
|
|
|
|
@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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3076 Location: NYC,USA
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3076 Location: NYC,USA
|
|
|
|
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 |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008 Posts: 193 Location: chennai
|
|
|
|
@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 |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008 Posts: 193 Location: chennai
|
|
|
|
@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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3076 Location: NYC,USA
|
|
|
|
You got it!! thanks. |
|
Back to top |
|
|
|