Execute Stored Procedures in Sybase for PX

[sck]

Hi All,
How do I execute Stored Procedure in Sybase from DS PX Jobs.

Thanks
SCK

[richdhan]

Hi SCK,

In DS 7.5 there is a Stored Procedure stage which can be used to execute stored procedures. If you are on a lower version you can probably execute the stored procedure through some work around but Iam sure that it will not return the output parameters.

HTH
--Rich

Pride comes before a fall
Humility comes before honour

[mandyli]

Hi SCK,


Using Stored procedure stage you can achieve this or create following server or parallel user defined routine through Datastage manager ,then call that routine in Before / After routine job parameter screen.

[b]Ex: calling routine [/b]

Before-Job subroutine: Input value:
SYBASESQL Exec procedure name

SYBASESQL is user defined routine name.



SYBASESQL is user defined routine name.


Following is routine script for SYBASESQL

/ *

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

* Setup ODBC DSN and UserID and Password
strDataSource = DSGetParamInfo(DSJ.ME,"DSN", DSJ.PARAMVALUE)
strODBCUserID = DSGetParamInfo(DSJ.ME,"USERID", DSJ.PARAMVALUE)
strODBCPassword = DSGetParamInfo(DSJ.ME,"PWD", DSJ.PARAMVALUE)



* InputArg is a string of the form ODBCStageName;SQLStatement
* Initialise BCI handles
henv = SQL.NULL.HENV
hdbc = SQL.NULL.HDBC
hstmt = SQL.NULL.HSTMT
hstmt1 = SQL.NULL.HSTMT
Connected = @FALSE

henv2 = SQL.NULL.HENV
hdbc2 = SQL.NULL.HDBC
hstmt2 = SQL.NULL.HSTMT
Connected2 = @FALSE

* Establish First connection to the DataSource
Ret = SQLAllocEnv(henv)
Function = 'SQLAllocEnv'
GoSub ErrHandler
Ret = SQLAllocConnect(henv,hdbc)
Function = 'SQLAllocConnect'
GoSub ErrHandler
Ret = SQLConnect(hdbc,strDataSource,strODBCUserID,strODBCPassword)
Function = 'SQLConnect'

GoSub ErrHandler
Connected = @TRUE

Ret = SQLSetConnectOption(hdbc, SQL.TX.PRIVATE, SQL.TX.PRIVATE.ON)

Function = 'SQLSetConnectOption(SQL.TX.PRIVATE)'
GoSub ErrHandler

Ret = SQLAllocStmt(hdbc, hstmt1)
Function = 'SQLAllocStmt'

GoSub ErrHandler

* Get SQL Statement to execute
SQLStatement1 = InputArg

* Call DSLogWarn(InputArg, "sybsql")


* Execute the first statement
Ret = SQLExecDirect(hstmt1, SQLStatement1)
Function = 'SQLExecDirect'
hstmt = hstmt1
GoSub ErrHandler

* Close Connection One now that we have finished with it
Ret = SQLTransact(henv,hdbc,SQL.COMMIT)
Function = 'SQLTransact'
hstmt = SQL.NULL.HSTMT
GoSub ErrHandler
* Turn of private transaction otherwise we can't disconnect without an error
Ret = SQLSetConnectOption(hdbc,SQL.TX.PRIVATE,SQL.TX.PRIVATE.OFF)
Function = "SQLSetConnectOption(SQL.PRIVATE.TX)"
GoSub ErrHandler
* Free any resources used by this subroutine
Ret = SQLFreeStmt(hstmt1, SQL.DROP)
Function = 'SQLFreeStmt'
hstmt = hstmt1
GoSub ErrHandler
hstmt1 = SQL.NULL.HSTMT
hstmt = SQL.NULL.HSTMT
Ret = SQLDisconnect(hdbc)
Function = 'SQLDisconnect'
GoSub ErrHandler
Connected = @FALSE
Ret = SQLFreeConnect(hdbc)
Function = 'SQLFreeConnect'
GoSub ErrHandler
hdbc = SQL.NULL.HDBC
Ret = SQLFreeEnv(henv)
Function = 'SQLFreeEnv'
GoSub ErrHandler
henv = SQL.NULL.HENV



NormalExit:
Ans = 0 ; * set this to non-zero to stop the stage/job
RETURN

ErrorExit:
Ans = 1
RETURN

ErrHandler:
BEGIN CASE
CASE Ret EQ SQL.SUCCESS OR Ret EQ SQL.NO.DATA.FOUND
NULL
CASE Ret EQ SQL.SUCCESS.WITH.INFO
InfoText = "ExecSQL call to ":Function:" returned informational message."
Call DSLogWarn(InfoText, "True")
Loop
Ret = SQLError(henv,hdbc,hstmt,sqlstate,dbms.code,errmsg)
Until Ret NE SQL.SUCCESS
InfoText := "<L>sqlstate=":sqlstate:", dbms.code=":dbms.code:"<L>":errmsg
Repeat
InfoText = Ereplace(InfoText,"<L>",CHAR(13):CHAR(10))
Print InfoText
CASE @TRUE
Call DSLogWarn(ErrorText, "False")
ErrorText = "True Loop":Function:" failed."
* Call DSLogWarn(ErrorText, "MyCopyOfSybsql")

*Loop
* Ret = SQLError(henv,hdbc,hstmt,sqlstate,dbms.code,errmsg)
*Until Ret NE SQL.SUCCESS
* ErrorText := "<L>sqlstate=":sqlstate:", dbms.code=":dbms.code:"<L>":errmsg
*Repeat
*ErrorText = Ereplace(ErrorText,"<L>",CHAR(13):CHAR(10))
*Print ErrorText
*If NOT(NUM(hstmt1)) Then Ignore = SQLFreeStmt(hstmt1,SQL.DROP)
*If NOT(NUM(hstmt2)) Then Ignore = SQLFreeStmt(hstmt2,SQL.DROP)
*If Connected Then
* * Turn of private transaction otherwise we can't disconnect without an error
* Ignore = SQLSetConnectOption(hdbc,SQL.TX.PRIVATE,SQL.TX.PRIVATE.OFF)
* Ignore = SQLDisconnect(hdbc)
*End
*If NOT(NUM(hdbc)) Then Ignore = SQLFreeConnect(hdbc)
*If NOT(NUM(henv)) Then Ignore = SQLFreeEnv(henv)
Ans = 1
ErrorCode = 1
Call DSLogFatal("SQL Failed",sybsql)


END CASE


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

//.........................................................................................................
Please try this.

Thanks
Man

[ray.wurlod]

This "solution" will only work for 30 days.

Then you will have to buy a licence for the ODBC driver, or for some other third party UNIX-based ODBC driver that you elect to purchase.

The branded ODBC drivers that ship with DataStage are licensed only for use with DataStage stages, not for use with BCI functions.