Deletion of Records

[Bilwakunj]

Hi Everybody,
I have the following scenario.
I am getting multiple records based on memeberID. so each memberID has multiple records. If the job gets aborted for a memberID I want to delete the other records for that memberID. How can i implement it?

Thanks in advance.

[sonia jacob]

Hi,

In case you have set Rows per Transaction (Commit level) to be greater than zero only then you would run into the problem of having records in the target table before it aborts.

you would probably need to think about deleting all records(or records of that particular) in the target in case of an abort because at a point of time an abort occurs i do not think you can be sure whether the complete set of records of a memberID has gone in.

Hope it helps.

sonia

[Bilwakunj]

Hi sonia,
Thanks for the same. I agree I will have to delete but how? Manually.. I am new to datastage so pardon me for my poor knowledge. I am trying to find it how to delete ?

Hi,

In case you have set Rows per Transaction (Commit level) to be greater than zero only then you would run into the problem of having records in the target table before it aborts.

you would probably need to think about deleting all records(or records of that particular) in the target in case of an abort because at a point of time an abort occurs i do not think you can be sure whether the complete set of records of a memberID has gone in.

Hope it helps.

sonia

[sonia jacob]

By setting the rows per transation to zero in the tab "transaction handling", you can make sure the commit happens only at the sucessful completion of the job. In this case you need not worry about deleting records etc.

But if the rows per transation is set to > 0 then,

1) one way to do it is to handle the same in the sequence job that calls your server job. The "job activity" stage has a tab called "Trigger" wherein you can see various expression types "OK", "Failed", "Warning" etc. One outputlink from the "job activity" that calls your job must have the expression type as set to "Failed" and it should go to a routine or job that deletes the records from the target table.

2) Another way to do is. In the "Properties" page of the job have a look at the "General" tab. You can specify an "After-job Subroutine" (MAKE SURE THAT THE CHECK BOX "only run after-job subroutine on sucessful job completion" IS LEFT UNCHECKED). Following is not a complete code, but probably would help you to come up with one.

$INCLUDE DSINCLUDE JOBCONTROL.H
JobHandle = DSJ.ME
JobSts = DSGetJobInfo(JobHandle,DSJ.JOBSTATUS)

If JobSts = DSJS.RUNFAILED then delete records


another sample code to make an odbc connection and run a query from a routine is given below

$include DSINCLUDE JOBCONTROL.H
$include UNIVERSE.INCLUDE ODBC.H

ErrorCode = 0 ; * set this to non-zero to stop the stage/job

RoutineName="RoutineName"
JOBNAME = DSJobName
StartDate=@Date

Quote = "'"


*----------------------------------------------------------------------------
* Get Connect String, User Name, password.
*----------------------------------------------------------------------------

odbcDSN = DSGetParamInfo(DSJ.ME, "CONNECTSTRING", DSJ.PARAMVALUE)
odbcLogOn = DSGetParamInfo(DSJ.ME, "USERNAME", DSJ.PARAMVALUE)
odbcPassword = DSGetParamInfo(DSJ.ME, "PASSWORD", DSJ.PARAMVALUE)


*----------------------------------------------------------------------------
* Establish the Environment and then Connect to ODBC.
*----------------------------------------------------------------------------

Status = SQLAllocEnv(odbcENV)

If Status <> 0 Then
call DSLogInfo("SQLAllocEnv Failed. Status is:":Status, RoutineName)
Goto FunctionEnd
End
Status = SQLAllocConnect(odbcENV, odbcConnectEnv)

If Status <> 0 Then
call DSLogInfo("SQLAllocConnect Failed. Status is:":Status, RoutineName)
Goto FunctionEnd
End

Status = SQLConnect(odbcConnectEnv, odbcDSN, odbcLogOn, odbcPassword)
If Status <> 0 Then
call DSLogInfo("SQLConnect Failed. Status is:":Status, RoutineName)
Goto FunctionEnd
End

Status = SQLAllocStmt(odbcConnectEnv, odbcStmtEnv)
If Status <> 0 Then
call DSLogInfo("SQLAllocStmt Failed. Status is:":Status, RoutineName)
Goto FunctionEnd
End
*--------------------------------------------------------------------
* Exceute the Insert statement for updating the Stg_Status to 'C'
*--------------------------------------------------------------------
Statement ="delete from table"
Status= SQLExecDirect(odbcStmtEnv, Statement)

Call DSLogInfo("Executed ":Statement, RoutineName)
If Status <> 0 Then
call DSLogInfo("SQLExecDirect Failed. Status is:":Status, RoutineName)
Goto FunctionEnd
End


Status = SQLFreeStmt(odbcStmtEnv,SQL.DROP)


FunctionEnd:
Status = SQLDisconnect(odbcConnectEnv)
Status = SQLFreeConnect(odbcConnectEnv)
Status = SQLFreeEnv(odbcENV)

[KeithM]

Stages like the ODBC stage do not have a delete option in the list of update actions. In order to delete you have select user defined sql and then write the sql statement yourself. Pass in the key column to your delete stage and code your statement similar to the following:

Code: Select all

Delete From "My_Table"  WHERE ("memberID " = ?)

I hope this helps.

[Bilwakunj]

Thanks .. I got it..

Stages like the ODBC stage do not have a delete option in the list of update actions. In order to delete you have select user defined sql and then write the sql statement yourself. Pass in the key column to your delete stage and code your statement similar to the following:

Code: Select all

Delete From "My_Table"  WHERE ("memberID " = ?)

I hope this helps.

[Bilwakunj]

Thanks a lot sonia...It's really helpful...

By setting the rows per transation to zero in the tab "transaction handling", you can make sure the commit happens only at the sucessful completion of the job. In this case you need not worry about deleting records etc.

But if the rows per transation is set to > 0 then,

1) one way to do it is to handle the same in the sequence job that calls your server job. The "job activity" stage has a tab called "Trigger" wherein you can see various expression types "OK", "Failed", "Warning" etc. One outputlink from the "job activity" that calls your job must have the expression type as set to "Failed" and it should go to a routine or job that deletes the records from the target table.

2) Another way to do is. In the "Properties" page of the job have a look at the "General" tab. You can specify an "After-job Subroutine" (MAKE SURE THAT THE CHECK BOX "only run after-job subroutine on sucessful job completion" IS LEFT UNCHECKED). Following is not a complete code, but probably would help you to come up with one.

$INCLUDE DSINCLUDE JOBCONTROL.H
JobHandle = DSJ.ME
JobSts = DSGetJobInfo(JobHandle,DSJ.JOBSTATUS)

If JobSts = DSJS.RUNFAILED then delete records


another sample code to make an odbc connection and run a query from a routine is given below

$include DSINCLUDE JOBCONTROL.H
$include UNIVERSE.INCLUDE ODBC.H

ErrorCode = 0 ; * set this to non-zero to stop the stage/job

RoutineName="RoutineName"
JOBNAME = DSJobName
StartDate=@Date

Quote = "'"


*----------------------------------------------------------------------------
* Get Connect String, User Name, password.
*----------------------------------------------------------------------------

odbcDSN = DSGetParamInfo(DSJ.ME, "CONNECTSTRING", DSJ.PARAMVALUE)
odbcLogOn = DSGetParamInfo(DSJ.ME, "USERNAME", DSJ.PARAMVALUE)
odbcPassword = DSGetParamInfo(DSJ.ME, "PASSWORD", DSJ.PARAMVALUE)


*----------------------------------------------------------------------------
* Establish the Environment and then Connect to ODBC.
*----------------------------------------------------------------------------

Status = SQLAllocEnv(odbcENV)

If Status <> 0 Then
call DSLogInfo("SQLAllocEnv Failed. Status is:":Status, RoutineName)
Goto FunctionEnd
End
Status = SQLAllocConnect(odbcENV, odbcConnectEnv)

If Status <> 0 Then
call DSLogInfo("SQLAllocConnect Failed. Status is:":Status, RoutineName)
Goto FunctionEnd
End

Status = SQLConnect(odbcConnectEnv, odbcDSN, odbcLogOn, odbcPassword)
If Status <> 0 Then
call DSLogInfo("SQLConnect Failed. Status is:":Status, RoutineName)
Goto FunctionEnd
End

Status = SQLAllocStmt(odbcConnectEnv, odbcStmtEnv)
If Status <> 0 Then
call DSLogInfo("SQLAllocStmt Failed. Status is:":Status, RoutineName)
Goto FunctionEnd
End
*--------------------------------------------------------------------
* Exceute the Insert statement for updating the Stg_Status to 'C'
*--------------------------------------------------------------------
Statement ="delete from table"
Status= SQLExecDirect(odbcStmtEnv, Statement)

Call DSLogInfo("Executed ":Statement, RoutineName)
If Status <> 0 Then
call DSLogInfo("SQLExecDirect Failed. Status is:":Status, RoutineName)
Goto FunctionEnd
End


Status = SQLFreeStmt(odbcStmtEnv,SQL.DROP)


FunctionEnd:
Status = SQLDisconnect(odbcConnectEnv)
Status = SQLFreeConnect(odbcConnectEnv)
Status = SQLFreeEnv(odbcENV)