ODBC stage problem

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
JPalatianos
Premium Member
Premium Member
Posts: 306
Joined: Wed Jun 21, 2006 11:41 am

ODBC stage problem

Post by JPalatianos »

Hi and Happy New Year to all!!
I have a simple job which is loading 500K rows of data from a flat file to a UDB table using the following Sequential stage====> Transformer=====> ODBC STage Whe I initially ran, I receive the following error message:
SQLSTATE=S1000, DBMS.CODE=-964
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for Windows, UNIX, and Linux]The transaction log for the database is full.

I then figured I would adjust the Rows per Transaction field from the default of "0". I've tried multiple combinations of Rows per Transaction and Parameter Array Size but always instantly get the same error now....
Project:ALM (NJROS1BVA0302)
Job name:ALM_Load_Int_Assmptn
Event #:768
Timestamp:1/4/2008 11:55:46 AM
Event type:Warning
User:PRUDENTIAL\X090842
Message:
ALM_Load_Int_Assmptn..xfm.outIntRtAssmptn: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO VALM.INT_RT_ASSMPTN(INT_RT_SEG_NO, INT_TRIAL_ID, INT_RT_TRIAL_YR_NO, EFF_DT, CAPTL_MRKET_SEGMENT_NO, INT_RT_AMT, LAST_ACTY_OPER_ID) VALUES (?,?,?,?,?,?,?)
SQLSTATE=08S01, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol driver]Unexpected Network Error. ErrNum = 10054

INT_RT_SEG_NO = 1
INT_TRIAL_ID = 1
INT_RT_TRIAL_YR_NO = 101
EFF_DT = 4
CAPTL_MRKET_SEGMENT_NO = 2007-11-30
INT_RT_AMT = 1
LAST_ACTY_OPER_ID = "0.0782"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 1
INT_RT_TRIAL_YR_NO = 2
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0535
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 1
INT_RT_TRIAL_YR_NO = 3
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0576
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 1
INT_RT_TRIAL_YR_NO = 4
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0607
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 1
INT_RT_TRIAL_YR_NO = 5
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0666
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 1
INT_RT_TRIAL_YR_NO = 6
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0674
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 1
INT_RT_TRIAL_YR_NO = 7
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0582
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 1
INT_RT_TRIAL_YR_NO = 8
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0679
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 1
INT_RT_TRIAL_YR_NO = 9
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0595
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 1
INT_RT_TRIAL_YR_NO = 10
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0572
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 2
INT_RT_TRIAL_YR_NO = 1
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0651
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 2
INT_RT_TRIAL_YR_NO = 2
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0549
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 2
INT_RT_TRIAL_YR_NO = 3
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.055
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 2
INT_RT_TRIAL_YR_NO = 4
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.062
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 2
INT_RT_TRIAL_YR_NO = 5
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0499
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 2
INT_RT_TRIAL_YR_NO = 6
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0541
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 2
INT_RT_TRIAL_YR_NO = 7
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0558
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 2
INT_RT_TRIAL_YR_NO = 8
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0606
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 2
INT_RT_TRIAL_YR_NO = 9
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0447
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 2
INT_RT_TRIAL_YR_NO = 10
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0355
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 3
INT_RT_TRIAL_YR_NO = 1
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0432
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 3
INT_RT_TRIAL_YR_NO = 2
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0455
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 3
INT_RT_TRIAL_YR_NO = 3
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0495
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 3
INT_RT_TRIAL_YR_NO = 4
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0419
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 3
INT_RT_TRIAL_YR_NO = 5
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0422
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 3
INT_RT_TRIAL_YR_NO = 6
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0294
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 3
INT_RT_TRIAL_YR_NO = 7
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0412
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 3
INT_RT_TRIAL_YR_NO = 8
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0296
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 3
INT_RT_TRIAL_YR_NO = 9
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0251
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 3
INT_RT_TRIAL_YR_NO = 10
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0291
LAST_ACTY_OPER_ID = "x126134"
INT_RT_SEG_NO = 1
INT_TRIAL_ID = 4
INT_RT_TRIAL_YR_NO = 1
EFF_DT = 2007-11-30
CAPTL_MRKET_SEGMENT_NO = 1
INT_RT_AMT = 0.0731
LAST_ACTY_OPER_ID = "x126134"

Any suggestions would be appreciated....
Thanks - - John
Top
JPalatianos
Premium Member
Premium Member
Posts: 306
Joined: Wed Jun 21, 2006 11:41 am

Post by JPalatianos »

The error we are actually getting once playing around with teh transaction handling is consitently"socket closed":

Project:ALM (NJROS1BVA0302)
Job name:ALM_Load_Int_Assmptn
Event #:8
Timestamp:1/4/2008 1:42:36 PM
Event type:Fatal
User:PRUDENTIAL\X090842
Message:
ALM_Load_Int_Assmptn..xfm.outIntRtAssmptn: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO VALM.INT_RT_ASSMPTN(INT_RT_SEG_NO, INT_TRIAL_ID, INT_RT_TRIAL_YR_NO, EFF_DT, CAPTL_MRKET_SEGMENT_NO, INT_RT_AMT, LAST_ACTY_OPER_ID) VALUES (?,?,?,?,?,?,?)
SQLSTATE=08S01, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol driver]Socket closed.
Top
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Contact ray.wurlod

Post by ray.wurlod »

The transaction log is full. DB2 will not accept any more transactions until it is either purged or enlarged. That's why it closes the connection.

Nothing to do with DataStage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Top
JPalatianos
Premium Member
Premium Member
Posts: 306
Joined: Wed Jun 21, 2006 11:41 am

Post by JPalatianos »

I don't think I was being clear......the initial problem was that my transaction log was filling up(Initial error in post). I had it enlarged and am trying to execute my datastage job with various combinations of "Rows per Transaction" and "Parameter array size" and now I keep getting the second error.....

Note: the job runs fine now with the default Rows per Transaction = 0 and Parameter array Size = 1. I am trying to do some commit control on the datastage side and when I sway rom these default I keep getting the error below.

Project:ALM (NJROS1BVA0302)
Job name:ALM_Load_Int_Assmptn
Event #:8
Timestamp:1/4/2008 1:42:36 PM
Event type:Fatal
User:PRUDENTIAL\X090842
Message:
ALM_Load_Int_Assmptn..xfm.outIntRtAssmptn: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO VALM.INT_RT_ASSMPTN(INT_RT_SEG_NO, INT_TRIAL_ID, INT_RT_TRIAL_YR_NO, EFF_DT, CAPTL_MRKET_SEGMENT_NO, INT_RT_AMT, LAST_ACTY_OPER_ID) VALUES (?,?,?,?,?,?,?)
SQLSTATE=08S01, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol driver]Socket closed.
Top
Post Reply

Return to “IBM<sup>®</sup> Infosphere DataStage Server Edition”