Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Select IDENTITY column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Vamshi Veludandi

New User


Joined: 17 Mar 2009
Posts: 23
Location: Bangalore

PostPosted: Fri Aug 31, 2012 6:26 pm    Post subject: Select IDENTITY column
Reply with quote

Hi,

We have a table for which the DDL is as follows:
Code:

CREATE TABLE             
  EMP_TAB
   ( Emp_No  INTEGER NOT NULL
          GENERATED ALWAYS             
          AS IDENTITY                   
    (  START WITH 1               
         INCREMENT BY 1             
         MINVALUE 1                 
         MAXVALUE 20000         
         NO CYCLE                   
         NO CACHE                   
         NO ORDER  ),
     E_code   CHAR(3)
   )


I tried to retrieve data from 2 columns of the table EMP_TAB.
Code:
SELECT Emp_no,
       '|',
       E_code
FROM EMP_TAB


and expected the output file to be of length 4+1+3 = 8 and the character '|' would be positioned at column no 5. But it didn't.

Code:
COMMAND ===>
       EMP-ID      D        E-CODE                                           
       4/BI        1/AN     3/AN                                             
       (1-4)       (5-5)    (6-8)                                             
       1---------- 2------- 3-------                                         
****** ***************************** TOP OF DATA ******************-CAPS OFF-*
000001           1            |                                               
000002           2            |                                               
000003           3            |                                               
000004           4            |                                               
****** **************************** BOTTOM OF DATA ****************-CAPS OFF-*

Only when I changed the layout, I was able to see the data correctly.
Code:
COMMAND ===>                                                  SCROLL ===> CSR
       EMP-ID      A        B        C        D        E-CODE                 
       4/BI        1/AN     1/AN     1/AN     1/AN     3/AN                   
       (1-4)       (5-5)    (6-6)    (7-7)    (8-8)    (9-11)                 
       1---------- 2------- 3------- 4------- 5------- 6-------               
****** ***************************** TOP OF DATA ******************-CAPS OFF-*
000001           1                            |        111                   
000002           2                            |        222                   
000003           3                            |        333                   
000004           4                            |        444                   
****** **************************** BOTTOM OF DATA ****************-CAPS OFF-*


Just wondering why 3 bytes of data has appeared adjacent to the IDENTITY column ?
Do we ever have to handle that data ?

Thanks,
Vamshi.
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10255
Location: italy

PostPosted: Fri Aug 31, 2012 7:10 pm    Post subject: Reply to: Select IDENTITY column
Reply with quote

Quote:
Only when I changed the layout,
icon_question.gif icon_question.gif icon_question.gif
Back to top
View user's profile Send private message
Vamshi Veludandi

New User


Joined: 17 Mar 2009
Posts: 23
Location: Bangalore

PostPosted: Sat Sep 01, 2012 12:40 pm    Post subject: Reply to: Select IDENTITY column
Reply with quote

Hi,
With the assumption that the output file created by the unload job would of length 8, I created a copybook with 3 fields (as mentioned in the select) and tried to view the file with the copybook layout.
But the data in the file didn't match the copybook layout correctly.
Therefore I inserted a field with 3 bytes after the first field making the whole copybook length as 11 bytes.
Only then was I able to map the data correctly.

Thanks,
Vamshi
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Sat Sep 01, 2012 8:24 pm    Post subject:
Reply with quote

did this '|' not come as varchar?

view the unload file with hex and let us see it
(no, do not use the utillity with the copybook
just a straight view of the unload file)
and use the following sql:
Code:

SELECT Emp_no,
       '|',
       E_code
FROM EMP_TAB
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Sep 03, 2012 2:48 pm    Post subject:
Reply with quote

'|' is probaly unloaded as a nullable varchar .
Record layout of the unload file would show up in SYSPUNCH if it wasn't set to dummy.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Mon Sep 03, 2012 3:09 pm    Post subject:
Reply with quote

for info:

SELECT CHAR('|',1)
FROM ...

would result in a 1 byte fixed length char string.

you could also use SUBSTR('|',1,1)
Back to top
View user's profile Send private message
Vamshi Veludandi

New User


Joined: 17 Mar 2009
Posts: 23
Location: Bangalore

PostPosted: Mon Sep 03, 2012 8:07 pm    Post subject: Reply to: Select IDENTITY column
Reply with quote

Dick / Guyc / Enrico,
Thanks a lot.

I really had no idea that whenever a hard coded value is given in the select statement, DB2 considers value within quotes as a VARCHAR field and a numeric value as INTEGER.

I even tried SELECT CHAR('|') FROM ... but no joy.
Only when I mentioned the length i.e. SELECT CHAR('|',1) FROM ...
was i able to get the expected result.

Thankyou all once again.

Regards,
Vamshi.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 6 Sun Nov 06, 2016 8:11 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am
No new posts Add system date inside of a PS file a... pramitdas DFSORT/ICETOOL 5 Sun Aug 28, 2016 12:06 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us