TekBytz

Products and Tools

Migrating Oracle Pivot Function to DB2/DASHDB

by on Aug.08, 2017, under Database

I have come across a situation where I need to migrate a ORACLE query which has a pivot function to DB2 equivalent SQL. The Pivot function is not available in DB2 or DASHDB. so was no direct solution in the internet and hence came up with my own solution

Sample Oracle Query with pivot function :

SELECT NM,DESC,ADM,USR,SUP
FROM
(
SELECT NM, DESC, GRP
FROM SRC_GRP
)
PIVOT
(
COUNT(GRP)
FOR GRP IN
(
'Admin' as ADM,
'User' as USR,
'Support' as SUP
)
)
ORDER BY NM

 

Converted equivalent DB2/DASHDB query:

SELECT NM,DESC,count(ADM) AS ADM,count(USR) AS USR,count(SUP) AS SUP
FROM
(
SELECT NM, DESC,
CASE WHEN GRP = 'Admin' THEN GRP END AS ADM,
CASE WHEN GRP = 'User' THEN GRP END AS USR,
CASE WHEN GRP = 'Support' THEN GRP END AS SUP
FROM SRC_GRP
)
group by NM, DESC
Order by NM

 

I was able to get the same output from both the queries.

Leave a Comment :, , , , , , more...

Datastage : Schema reconciliation detected a size mismatch in timestamp

by on Jul.31, 2017, under Error and Issues

Error Message: 

Schema reconciliation detected a size mismatch for column DT. When writing column DATETIME(fraction=6) into database column DATETIME(fraction=0), truncation, loss of precision, data corruption or padding can occur.

 

Resolution : 

This issue occurs when the source stream is read as times tamp with microseconds and when writing into target the time stamp field does not have microseconds scale.  There is no direct functions to truncate the microseconds scale, as a work-around below method can be used.

StringToTimestamp(TimestampToString(from_xfm.DT,"%yyyy-%mm-%dd %hh:%nn:%ss") ,"%yyyy-%mm-%dd %hh:%nn:%ss")

Leave a Comment :, , , , more...

SQL0668N Operation not allowed for reason code “3” on table

by on Jul.24, 2017, under Error and Issues

Error:

SQLExecute reported: SQLSTATE = 57016: Native Error Code = -668: Msg = [IBM][CLI Driver][DB2/LINUXX8664] SQL0668N Operation not allowed for reason code “3” on table “XXX.XXXX”. SQLSTATE=57016

 

Resolution:

After doing several research the issue was the table was marked in check pending state due to bad bulk load without terminate in load statement.

Loading a null file to the table with the terminate command will clear the issue.

load from /dev/null of del terminate into XXX.XXXX

Sample :

db2 => LOAD FROM /DEV/NULL OF DEL TERMINATE INTO XXX.XXXX
SQL3110N The utility has completed processing. "0" rows were read from the
input file.


Number of rows read = 0
Number of rows skipped = 0
Number of rows loaded = 0
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 0

 

Platform :

Datastage 11.5 on Linux &

DB2 10.5 on Linux

Leave a Comment :, , , , more...

Datastage : DB2 Lookup Stage : SAX parser exception thrown: The input ended before all started tags were ended

by on Jun.30, 2017, under Error and Issues

Error :

Unrecognized argument: variant=’9.1\’,
library=ccdb2,
version=1.0,
variantlist=\’V1;9.1::ccdb2\’,
versionlist=\’1.0\’,
name=DB2Connector
}’

SAX parser exception thrown: The input ended before all started tags were ended. Last tag started was ‘before’ (CC_PropertySet::fatalError, file CC_PropertySet.cpp, line 2,236)

 

Resolution:

There are lot of references in google that the similar kind of error occurred in the XML stage and it it XML stage related error.  But in my case this occurred in the DB2 lookup stage. After serval analysis, I found that, there was a environment variable defined for the table name and during the migration the environment variable was not added in the job parameter.

example :

SELECT COL1, COL2  FROM #$SrcTbl#

$SrcTbl was missing in the job parameter list.

This looks to weird since if this was in DB2 source or target stage, the error will be table not found.

 

Platform :

Datastage 11.5 on Linux

 

 

Leave a Comment :, , more...

DataStage : DB2 Stage : Failure during execution of operator logic

by on Jun.30, 2017, under Error and Issues

Error Details :
Failure during execution of operator logic.
Output 0 produced 0 records.
APT_Decimal::assignFromString: invalid format for the source string. Expecting trailing NUL, space, or tab character(s) got ‘E’

Resolution:
One of the column which has the SQL type Decimal has the value “5E+7” which was not able convert to Decimal value. To eliminate this error read the column as String

This could happen when the decimal field is computed. To revert back to decimal field use explicit cast function

CAST(CAST(DISBURSED_AMOUNT AS FLOAT) AS DECIMAL(31,2))

Platform : 

DataStage 11.5 running on linux
DASHDB 10.5 running on linux

Leave a Comment :, , , more...

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!