problem with DB2 stage

[aparnanarale]

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.

[vigneshra]

Hi Aparna,

Did you add all the parameters in your job and set the values for the parameters correctly? Just check it! It may be a source for your problem and it is always least noticed.

[rajiivnb]

Hi,
Error calling subroutine in this case is usually coz of the ODBC not properly set to take the dsn properly.Check for all the connections.

Regards,
Rajii

[Sainath.Srinivasan]

Check the number of columns defined in the stage. There may be a mismatch in the datatype.

Try removing the word 'INNER JOIN' as it is the default join in any query. ODBC may probably not recognize that.

[peterbaun]

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

[sun786]

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.

[coxm19]

I had a similar problem with "junk". Make sure you have translation turned on for your ODBC data source. On Translation tab, check "Convert binary data to text".

Do you have any problems with data truncating to 10 characters?

[sharath]

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().

[dsxuserrio]

Aparna
Your query looks incomplete.
I guess you missed to post a few lines for the last table

#pUKDWSCHEMA#.DWH_DS_CNTL_RUN AS A7

There is no joining clause.

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

[Sainath.Srinivasan]

I assume that A7 is a single line table and the join in the where clause for A7 is enough to fetch and compare the one record.

[aparnanarale]

Thanks all.
Actually there was a problem with the data type.Datastage can not take Int 16 .It should be bigint 16.