problem with DB2 stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 6
- Joined: Mon Feb 07, 2005 11:54 pm
problem with DB2 stage
I have the following query in DB2
-------------------------------------------------------------------------------------
COLUMN NAME DATA TYPE LENGTH
--------------------------------------------------------------------------------------
TSS_CUST_ID InTEGER 8
RPS_ACCT_ID_14 InTEGER 16
LMT_VAL DECIMAL 15,2
CLR_BAL DECIAML 15,2
LST_CR_TRAN_DT DATE 10
The values of the date Parameters are as follows
pFUTURETS = 9999-12-31-23.59.59.999999
pFUTEDT = 9999-12-31
SELECT
A3.TSS_CUST_ID,
A1.RPS_ACCT_ID_14,
A4.LMT_VAL,A5.CLR_BAL,
CHAR(A6.LST_CR_TRAN_DT,ISO),
CHAR(A6.LST_DR_TRAN_DT,ISO)
FROM
#pUKDWSCHEMA#.DWH_ARRG_XREF AS A1
INNER JOIN
#pUKDWSCHEMA#.DWH_IP_ARRG_RELN AS A2
ON A1.ARRG_ID=A2.ARRG_ID
INNER JOIN
#pUKDWSCHEMA#.DWH_IP_XREF AS A3
ON A2.IP_ID = A3.IP_ID
INNER JOIN
#pUKDWSCHEMA#.DWH_PRAR_LMT AS A4
ON A1.ARRG_ID=A4.ARRG_ID
INNER JOIN
#pUKDWSCHEMA#.DWH_DLY_ACCT_BAL AS A5
ON A1.ARRG_ID=A5.ARRG_ID
INNER JOIN
#pUKDWSCHEMA#.DWH_ACCT_ARRG AS A6
ON A1.ARRG_ID=A6.ARRG_ID,
#pUKDWSCHEMA#.DWH_DS_CNTL_RUN AS A7
WHERE
A3.TSS_CUST_ID IS NOT NULL
AND
(A1.REC_TS >A7.CNTL_JOB_END_TS
AND
A1.REC_TS <='#pFUTURETS#')
AND
A6.REC_EDT='#pFUTEDT#'
AND
A4.PRAR_LMT_TYCD='1'
This query is working fine in DB2 environment.But in the DS when I am trying to view the data thro' DB2 stage for the same query,It is giving some junk values.Also it is giving some error:Error calling subroutine:DSR_EXECJOb(Action=5).
Please do let me know what is the problem ? Your help would be appreciated ASAP.
-------------------------------------------------------------------------------------
COLUMN NAME DATA TYPE LENGTH
--------------------------------------------------------------------------------------
TSS_CUST_ID InTEGER 8
RPS_ACCT_ID_14 InTEGER 16
LMT_VAL DECIMAL 15,2
CLR_BAL DECIAML 15,2
LST_CR_TRAN_DT DATE 10
The values of the date Parameters are as follows
pFUTURETS = 9999-12-31-23.59.59.999999
pFUTEDT = 9999-12-31
SELECT
A3.TSS_CUST_ID,
A1.RPS_ACCT_ID_14,
A4.LMT_VAL,A5.CLR_BAL,
CHAR(A6.LST_CR_TRAN_DT,ISO),
CHAR(A6.LST_DR_TRAN_DT,ISO)
FROM
#pUKDWSCHEMA#.DWH_ARRG_XREF AS A1
INNER JOIN
#pUKDWSCHEMA#.DWH_IP_ARRG_RELN AS A2
ON A1.ARRG_ID=A2.ARRG_ID
INNER JOIN
#pUKDWSCHEMA#.DWH_IP_XREF AS A3
ON A2.IP_ID = A3.IP_ID
INNER JOIN
#pUKDWSCHEMA#.DWH_PRAR_LMT AS A4
ON A1.ARRG_ID=A4.ARRG_ID
INNER JOIN
#pUKDWSCHEMA#.DWH_DLY_ACCT_BAL AS A5
ON A1.ARRG_ID=A5.ARRG_ID
INNER JOIN
#pUKDWSCHEMA#.DWH_ACCT_ARRG AS A6
ON A1.ARRG_ID=A6.ARRG_ID,
#pUKDWSCHEMA#.DWH_DS_CNTL_RUN AS A7
WHERE
A3.TSS_CUST_ID IS NOT NULL
AND
(A1.REC_TS >A7.CNTL_JOB_END_TS
AND
A1.REC_TS <='#pFUTURETS#')
AND
A6.REC_EDT='#pFUTEDT#'
AND
A4.PRAR_LMT_TYCD='1'
This query is working fine in DB2 environment.But in the DS when I am trying to view the data thro' DB2 stage for the same query,It is giving some junk values.Also it is giving some error:Error calling subroutine:DSR_EXECJOb(Action=5).
Please do let me know what is the problem ? Your help would be appreciated ASAP.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Hi -
I don't have a clear answer to your question but -
You mention 5 fields in your field description - in your select you have 6 fields (this should give another error if this is the case though).
Try to make your sql as simple as possible to determine wheter it is a connection problem or if the sql somehow creates the problem.
I cannot see whether you use custom sql or not. In DS 7.x (or at least in 7.5 which I have tested) there is a bug in the DB2 plugin if you have anything in the derivation field - it will give strange results at design time - the job will work fine at run time though. So if you have
CHAR(A6.LST_DR_TRAN_DT,ISO)
in the derivation field it would probably cause you problems.
If it is not a configuration issue I would test further whether your conversions - CHAR(A6.LST_CR_TRAN_DT,ISO) and CHAR(A6.LST_DR_TRAN_DT,ISO) are creating problems.
HTH
Regards
Peter
I don't have a clear answer to your question but -
You mention 5 fields in your field description - in your select you have 6 fields (this should give another error if this is the case though).
Try to make your sql as simple as possible to determine wheter it is a connection problem or if the sql somehow creates the problem.
I cannot see whether you use custom sql or not. In DS 7.x (or at least in 7.5 which I have tested) there is a bug in the DB2 plugin if you have anything in the derivation field - it will give strange results at design time - the job will work fine at run time though. So if you have
CHAR(A6.LST_DR_TRAN_DT,ISO)
in the derivation field it would probably cause you problems.
If it is not a configuration issue I would test further whether your conversions - CHAR(A6.LST_CR_TRAN_DT,ISO) and CHAR(A6.LST_DR_TRAN_DT,ISO) are creating problems.
HTH
Regards
Peter
I understand that you get junk values when you view data.
That should be issue with the data type defined in the stage for the feilds.
Try chaging the data type "date" to numeric.
I had char data which I converted to varchar( feild_name,feild_length,37)
and it worked.
First try viewing data only with select clause only for one table then other table.
Just try in this direction and try viewing data.
That should be issue with the data type defined in the stage for the feilds.
Try chaging the data type "date" to numeric.
I had char data which I converted to varchar( feild_name,feild_length,37)
and it worked.
First try viewing data only with select clause only for one table then other table.
Just try in this direction and try viewing data.
Re: problem with DB2 stage
DB2/DRS stage has always acted wierdly in DSPX, My best possible guess would be , you might have encountered this error in sql because of not handling the null values..In DB2 if you dont handle the nulls you will be thrown with exceptions..Try using this function which handles nulls COALESCE().
-
- Participant
- Posts: 82
- Joined: Thu Dec 02, 2004 10:27 pm
- Location: INDIA
Aparna
Your query looks incomplete.
I guess you missed to post a few lines for the last table
Can you post a few rows from your View Data windwo?? Post the ones that are wrong.
Did you run the job or you just viewed the data.
As suggested by other poster use coalesce for nullable fields.
Thanks
Your query looks incomplete.
I guess you missed to post a few lines for the last table
There is no joining clause.#pUKDWSCHEMA#.DWH_DS_CNTL_RUN AS A7
Can you post a few rows from your View Data windwo?? Post the ones that are wrong.
Did you run the job or you just viewed the data.
As suggested by other poster use coalesce for nullable fields.
Thanks
dsxuserrio
Kannan.N
Bangalore,INDIA
Kannan.N
Bangalore,INDIA
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 6
- Joined: Mon Feb 07, 2005 11:54 pm