Facing issues when using DSNTIAUL

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
User avatar
thamodharan
Member
Posts: 21
Joined: Tue Oct 21, 2008 4:45 pm
Location: chennai

Facing issues when using DSNTIAUL

Post by thamodharan » Sat Sep 24, 2011 12:33 pm

Hi All,

I am using DSNTIAUL utility to unload data and the query used to unload data is somewhat similar to the following query,

Select shop_id,
shop_name,
(select max(shop_last_pur_ts)
from shop_pur_det
where pur_type = 'POS'),
shop_addr1,
shop_addr2
from Shop_det
where shop_city_cd = 'NC'

Problem Description:

after successful completion of the unload step,

all the columns from the table are unloaded to the file correctly, except the third column in the above query.

That timestamp column(max(shop_last_pur_ts)) was unloaded as 27 bytes, the 27th byte being null. I need your suggestion to get this timestamp in the output file with only 26 bytes.

Additional information:

Shop_last_pur_ts was defined as TIMESTAMP type also NOT NULL.

I had tried to convert that whole column into Char type to take only 26 char of that timestamp. but didn't work for me. when i tried the following
again i got the timestamp value in that file as 26+1 bytes

(select substr(char(max(shop_last_pur_ts)),1,26) from shop_pur_det where pur_type = 'POS').

when i tried the following i got 25+1

(select substr(char(max(shop_last_pur_ts)),1,25) from shop_pur_det where pur_type = 'POS')

Please suggest me a way to have this problem fixed.

Thanks in advance

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Sat Sep 24, 2011 3:15 pm

you receive a null indicator when the result of the select for a column could potentially be null.

(select max(shop_last_pur_ts)
from shop_pur_det
where pur_type = 'POS'),

could be null. so try using case, value or coalease to insure you never receive a null from the max function.

has nothing to do with the column being not null. it is the result of the scalar function MAX. read the manual concerning MAX.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

User avatar
thamodharan
Member
Posts: 21
Joined: Tue Oct 21, 2008 4:45 pm
Location: chennai

Post by thamodharan » Sun Sep 25, 2011 11:09 am

Hi Dick,
(select max(shop_last_pur_ts)
from shop_pur_det
where pur_type = 'POS'),

could be null.
The above field has never been a NULL. It was 27 bytes instead of 26 bytes. First 26 bytes were having the Timestamp and the 27th byte was having no value. Please suggest me how to get only timestamp(26 bytes).

I will try using Case, Value or Coalease function as well and let you know if that worked.

Thanks

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Sun Sep 25, 2011 1:09 pm

The above field has never been a NULL
DB2 does not have fields, it has columns


dbz wrote:has nothing to do with the column being not null. it is the result of the scalar function MAX. read the manual concerning MAX.
improve your reading skills.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others