DB2 stored procedure

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
dsuser_cai
Premium Member
Premium Member
Posts: 151
Joined: Fri Feb 13, 2009 4:19 pm

DB2 stored procedure

Post by dsuser_cai »

Hi

I have a DB2 stored proc, and im passing a parameter (150 char length) and it returns an output of 29500 length. Also the guy who created the stored proc gave me a copy book (output format).

In my job im using a stored proc stage to call this stored proc. Im passing the parameter manually 91 parameter -150 char length, just to test). Its running fine without any warnings or fatal error, but the output is kind different. that is, im able to see only the first 100 chars and anything after that is populated with space.
This is the command im using
call stord_proc_name(input_param,?);

this works fine in datastage and in DB2 command editor, but boh are giving me only the first 150 chars, the remaining are only spaces. but when the team who wrote the stored proc runs it for the same parameters, they are able to see the entire output... can somebody help me. Please let me know if you need the copy book layout.
Thanks
Karthick
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

So... another attempt at this topic.

If you think it would help, post the copy book.
-craig

"You can never have too many knives" -- Logan Nine Fingers
dsuser_cai
Premium Member
Premium Member
Posts: 151
Joined: Fri Feb 13, 2009 4:19 pm

Post by dsuser_cai »

Hi

Heres the part from copy book where the field is located:

10 DOL-CUST-COVG-DETAIL.
15 DOL-CUST-ACF-COVG-COUNT PIC 9(02).
15 DOL-CUST-ACF-COVG OCCURS 50 TIMES.
20 DOL-CUST-BEN-OPT PIC X(05).
20 DOL-CUST-CCF-PKG-TY PIC X(06).
20 DOL-CUST-FILL1 PIC X(09).
20 DOL-CUST-PRODT-TY PIC X(06).
20 DOL-CUST-PRODT-GRP-TY PIC X(06).
20 DOL-CUST-ACF-EFF-DT PIC X(10).
20 DOL-CUST-ACF-CAN-DT PIC X(10).
20 DOL-CUST-MED-ACF-IND PIC X(01).
20 DOL-CUST-DENT-ACF-IND PIC X(01).
20 DOL-CUST-POS-ACF-IND PIC X(01).
20 DOL-CUST-FILL2 PIC X(25).

I need only 20 DOL-CUST-MED-ACF-IND PIC X(01). field.

There are many level 5 and level 10 records before this.
Thanks
Karthick
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Explore the "drop on import" property for the other fields.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
dsuser_cai
Premium Member
Premium Member
Posts: 151
Joined: Fri Feb 13, 2009 4:19 pm

Post by dsuser_cai »

Hi

We opened a ticket with IBM to fix this. And here is what we found:
1) the stored proc output had some low values (starting from position 172), so Datastage was truncating everything after that point.

2) the IBM representative said that datastage will not be able to handle these low values.

3) So as a work around, the Stored proc was modified to convert the low values into Spaces. After converting the low values into spaces, DataStage was able to copture the entire 29500 bytes of the output.

Thanks
Thanks
Karthick
Post Reply