Deleting records from a table - Fatal Error
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 247
- Joined: Thu Apr 27, 2006 6:38 am
- Location: Hyderabad
Deleting records from a table - Fatal Error
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Check the first entry for the job in the job log, to determine the values that were passed for the parameters.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 247
- Joined: Thu Apr 27, 2006 6:38 am
- Location: Hyderabad
[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.
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.
-
- Participant
- Posts: 247
- Joined: Thu Apr 27, 2006 6:38 am
- Location: Hyderabad
-
- Participant
- Posts: 247
- Joined: Thu Apr 27, 2006 6:38 am
- Location: Hyderabad
-
- Participant
- Posts: 247
- Joined: Thu Apr 27, 2006 6:38 am
- Location: Hyderabad
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...
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...
Hi
bit confused how it works. u have any column BRCK_MRTR_STR_I in DLY_PULL_AGG
Thanks
sanjay
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...
-
- Participant
- Posts: 247
- Joined: Thu Apr 27, 2006 6:38 am
- Location: Hyderabad