Deleting records from a table - Fatal Error

devidotcom · Post by **devidotcom** » Wed Jul 04, 2007 6:06 am

I am trying to delete just two rows from a DB2 table which as to meet a criteria of a date range. The first stage is a row generator which generates 0 rows to initiate the job the next stage is the DB2 stage which deletes the records

DELETE FROM #$PL_SCHEMA_NAME#.DLY_PULL_AGG
WHERE
PULL_BTCH_CRTE_D BETWEEN '#JPM_FROM_DATE#' AND '#JPM_TO_DATE#'
WITH UR

Write Method: Delete Rows
Delete Rows Mode: user-defined query
Table: #$PL_SCHEMA_NAME#.DLY_PULL_AGG
Delete SQL: would be the above query

I get the following fatal error:
Could you help me here
Db2udb_DLY_PULL_AGG: Unable to convert the adapter spec ostrstream object with a value of `' to ustring.

ray.wurlod · Post by **ray.wurlod** » Wed Jul 04, 2007 1:24 pm

What are the data types of the columns mentioned in the WHERE clause?

Check the first entry for the job in the job log, to determine the values that were passed for the parameters.

devidotcom · Post by **devidotcom** » Wed Jul 04, 2007 9:58 pm

[quote="ray.wurlod"]What are the data types of the columns mentioned in the WHERE clause?

Check the first entry for the job in the job log, to determine the values that were passed for the parameters. ...[/quote]

The datatype of PULL_BTCH_CRTE_D is date and the job parameters passed has the values 2007-06-04 and 2007-06-05.

I have even tried to remove the where clause and use the below two SQLs separately to see the output. I yet get the same error.

DELETE FROM #$PL_SCHEMA_NAME#.DLY_PULL_AGG
and
DELETE FROM SEDM1101.DLY_PULL_AGG

Checked the first job log... all the values are passed correctly to the parameters.

Could please help me find a solution.

devidotcom · Post by **devidotcom** » Thu Jul 05, 2007 3:13 am

Any solutions?

chulett · Post by **chulett** » Thu Jul 05, 2007 6:52 am

If anyone had anything, they would post. That error is odd enough, I'd suggest you open a case with your official Support provider.

devidotcom · Post by **devidotcom** » Thu Jul 05, 2007 11:05 pm

Thank you... Have raised a ticket with the ETL Admin here. Hope to get some assistance... Will keep the ticket updated.

DSguru2B · Post by **DSguru2B** » Fri Jul 06, 2007 7:43 pm

Try a where condition of where 1=1 just for debugging purposes. Consequently, delete the job and rebuild it. Sometimes that gets rid of insane errors.

devidotcom · Post by **devidotcom** » Tue Jul 10, 2007 1:26 am

As mentioned earlier I had raised a ticket with the ETL Admin here.

The query which was used earlier
DELETE FROM #$PL_SCHEMA_NAME#.DLY_PULL_AGG
WHERE
PULL_BTCH_CRTE_D BETWEEN '#JPM_FROM_DATE#' AND '#JPM_TO_DATE#'
WITH UR

Is now been modified to

DELETE FROM #$PL_SCHEMA_NAME#.DLY_PULL_AGG
WHERE
PULL_BTCH_CRTE_D BETWEEN '#JPM_FROM_DATE#' AND '#JPM_TO_DATE#' OR (ORCHESTRATE.BRCK_MRTR_STR_I = -1)

which helps in deleting rows from the table.
The row generator generates only one row and BRCK_MRTR_STR_I is a key and which is never 0. In this case it fails and then satisfies the date range condition.

I really don't know how this works but the records gets deleted as per the date range. It is strange to see that the query is waiting for some rows to come from the source stage to help it satify the other condition.
Could anyone throw some light...

Thanks anyways for all the support I always get from DSXCHANGE site...

sanjay · Post by **sanjay** » Tue Jul 10, 2007 2:01 am

Hi

bit confused how it works. u have any column BRCK_MRTR_STR_I in DLY_PULL_AGG

Thanks
sanjay

devidotcom wrote:As mentioned earlier I had raised a ticket with the ETL Admin here.

The query which was used earlier
DELETE FROM #$PL_SCHEMA_NAME#.DLY_PULL_AGG
WHERE
PULL_BTCH_CRTE_D BETWEEN '#JPM_FROM_DATE#' AND '#JPM_TO_DATE#'
WITH UR

Is now been modified to

DELETE FROM #$PL_SCHEMA_NAME#.DLY_PULL_AGG
WHERE
PULL_BTCH_CRTE_D BETWEEN '#JPM_FROM_DATE#' AND '#JPM_TO_DATE#' OR (ORCHESTRATE.BRCK_MRTR_STR_I = -1)

which helps in deleting rows from the table.
The row generator generates only one row and BRCK_MRTR_STR_I is a key and which is never 0. In this case it fails and then satisfies the date range condition.

I really don't know how this works but the records gets deleted as per the date range. It is strange to see that the query is waiting for some rows to come from the source stage to help it satify the other condition.
Could anyone throw some light...

Thanks anyways for all the support I always get from DSXCHANGE site...

devidotcom · Post by **devidotcom** » Tue Jul 10, 2007 2:05 am

Yes I have that column in the table DLY_PULL_AGG but it is not one of the condition I am looking at.