Connecting to Sql server 2008 from Server routine

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
boppanakrishna
Participant
Posts: 106
Joined: Thu Jul 27, 2006 10:05 pm
Location: Mumbai

Post by boppanakrishna »

HI Ray,

Code: Select all

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

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


$ENDIF 



*---- Start processing 

strOutPath = DSGetParamInfo(DSJ.ME, "RP", DSJ.PARAMVALUE) 
paths=DSGetParamInfo(DSJ.ME, "Patha", DSJ.PARAMVALUE) 


*---- Opening the File 

CntFilename = DSGetParamInfo(DSJ.ME, "JPM_ROUTINE_FILENAME", DSJ.PARAMVALUE) 
strFileName = strOutPath:"/":CntFilename 


*--Call DSLogInfo("Input link":strFileName,DSJ.ME) 

OpenSeq strFileName To objFileVar 

Else 

Create objFileVar 

Else 

ErrorCode = 1 


WeofSeq objFileVar 

End 

End 

WeofSeq objFileVar 

*---- Getting the link count for Input link 

Call DSLogInfo("Input link",DSJ.ME) 
iReadInputRecCount=0 
InputStageName1 = DSGetParamInfo(DSJ.ME,"JPM_INPUT_STAGE", DSJ.PARAMVALUE) 
InputLinkName1 = DSGetParamInfo(DSJ.ME, "JPM_INPUT_LINK", DSJ.PARAMVALUE) 
iReadInputRecCount1 = DSGetLinkInfo(DSJ.ME,InputStageName1,InputLinkName1,DSJ.LINKROWCOUNT) 
iReadInputRecCount=iReadInputRecCount1 

*----Checking if reject link exists or not 

Reject_Status = 0 

Reject_Status = DSGetParamInfo(DSJ.ME, "JPM_REJECT_STATUS", DSJ.PARAMVALUE) 

If(Reject_Status >0) Then 

*---- Getting the link count for Reject link 

Call DSLogInfo("Reject link",DSJ.ME) 
iReadRejCount = 0 
RejectStageName1 = DSGetParamInfo(DSJ.ME,"JPM_REJECT_STAGE1", DSJ.PARAMVALUE) 
RejectLinkName1 = DSGetParamInfo(DSJ.ME, "JPM_REJECT_LINK1", DSJ.PARAMVALUE) 
iReadRejCount1 = DSGetLinkInfo(DSJ.ME,RejectStageName1,RejectLinkName1,DSJ.LINKROWCOUNT) 
Call DSLogInfo("Reject Link1:" : iReadRejCount1,DSJ.ME) 

iReadRejCount = iReadRejCount1 

End Else 

iReadRejCount = 0 

End 

Call DSLogInfo("--------------JOB STATISTICS-------------",DSJ.ME) 

Call DSLogInfo("Input Record Count :":iReadInputRecCount,DSJ.ME) 

Call DSLogInfo("Reject Record Count :":iReadRejCount,DSJ.ME) 

*---- Getting the required counts from the Output link 

ProcessStageName= DSGetParamInfo(DSJ.ME,"JPM_OUTPUT_STAGE", DSJ.PARAMVALUE) 
ProcessLinkName = DSGetParamInfo(DSJ.ME, "JPM_OUTPUT_LINK", DSJ.PARAMVALUE) 
iProcessRecCount = DSGetLinkInfo(DSJ.ME,ProcessStageName,ProcessLinkName,DSJ.LINKROWCOUNT) 

strRecCount = FMT(iProcessRecCount,"10L") 

Call DSLogInfo("Output Record Count : " :strRecCount, DSJ.ME) 


iTotalRecordCnt = iProcessRecCount+iReadRejCount 

If ((iTotalRecordCnt = iReadInputRecCount) ) Then 

fthresholdSts= "SUCCESS" 

End Else 

fthresholdSts = "FAILURE" 

End 

*---- Other Parameters 

JobName = DSGetJobInfo (DSJ.ME, DSJ.JOBNAME) 
JobStartTime = DSGetJobInfo (DSJ.ME,DSJ.JOBSTARTTIMESTAMP) 
JobEndTime = DSGetJobInfo (DSJ.ME,DSJ.JOBLASTTIMESTAMP) 
ProjectID = DSGetParamInfo(DSJ.ME,"$HCP_SHARP_DS_PROJ", DSJ.PARAMVALUE) 
SequenceName = DSGetParamInfo(DSJ.ME,"JPM_DS_SEQ", DSJ.PARAMVALUE) 
MetricDescription= DSGetParamInfo(DSJ.ME,"JPM_METRIC_DESC", DSJ.PARAMVALUE) 

*---- Wrting the Reonciliation count into a file in format ProjectID,SequenceName,Job name,JobStartTime,JobEndTime,Input Records,Success records,Reject records,MetricValue,MetricDescription,Comments 

strReconCount = ProjectID:',':SequenceName:',':JobName:',':FMT(JobStartTime,"20L"):',':FMT(JobEndTime,"20L"):',':FMT(iReadInputRecCount,"10L"):',':FMT(iProcessRecCount,"10L"):',':FMT(iReadRejCount,"10L"):',':fthresholdSts 

WriteSeqF strReconCount To objFileVar Then 

End 

*---- Check for threshold and abort the job 

If ((iTotalRecordCnt = iReadInputRecCount) ) Then 

Call DSLogInfo("The records matched with the Input and Output and Rejects ",DSJ.ME) 

End Else 

Call DSLogFatal ("The records count did not match with the Input and Output and Rejects ",DSJ.ME) 

End 

*---- Check for Zero Reject Record count 

if iReadRejCount<=0 then 

Call DSLogInfo("No 'Reject Records' are present") 


End 


*-- Load into Database 
*-- Path=C:\'Program Files'\'Microsoft SQL Server'\90\Tools\Binn 

InsertStmt="INSERT INTO DATABASENAME.dbo.t values ('" : SequenceName: "');" 
SQLCmd='"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD" -S DATABASENAME -U USERNAME -P PASSWORD -q "' : InsertStmt : '"' 
Call DSExecute('NT',SQLCmd,Output,RtnCode)

Everythin in the code is working except connecting to Database.

Routine works 100% fine while loading the data to a sequential file and also to the Log

Please check the last 3 lines of the code and suggest

Many thanks in advance
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

ray.wurlod wrote:This would be so much easier to read if it were wrapped in Code tags rather than Quote tags. Can you please edit that last post accordingly?
Done... didn't really help, though, as no formatting survived the journey. :(
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Contact ray.wurlod

Post by ray.wurlod »

Was there any output or return code from DSExecute?

If the INSERT statement itself is hanging in SQL Server, have your DBA look at the session there and see if there's anything to be seen.

If you're using a Microsoft ODBC driver you might consider rewriting the last three lines to use BCI functions, at least to determine exactly where the problem is occurring (for example on connection, on preparing the SQL, on executing the SQL, etc.)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Top
boppanakrishna
Participant
Posts: 106
Joined: Thu Jul 27, 2006 10:05 pm
Location: Mumbai

Post by boppanakrishna »

Hi Ray,

I did check with my DBA, and it seems like its not connecting to Database.

None of the activity is triggered after the routine has started running.

It seems like its problem with the statement
SQLCmd='"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD" -S DATABASENAME -U USERNAME -P PASSWORD -q "' : InsertStmt : '"'
I am not familiar with BCI functions,

If possible please help me out with these.

Is there any other way/syntax to connect SQL server from Routine.

Many thanks for your time
Top
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Contact ray.wurlod

Post by ray.wurlod »

Not sure what DATABASENAME is in your context. Is it an ODBC data source name?

Search DSXchange for examples of how to use BCI functions to execute SQL over an ODBC connection.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Top
boppanakrishna
Participant
Posts: 106
Joined: Thu Jul 27, 2006 10:05 pm
Location: Mumbai

Post by boppanakrishna »

Hi Ray,
"DATBASENAME" in my context is actual database name in my SQL Server

which is 'GMFIT"

In my actual coding i have used this GMFIT,

I just modifed to DATABASENAME while posting so that there won't be any confusion.


If possible can you let me know the syntax to use ODBC connection for connecting to SQL server from the Routine


Regards
Kris
Top
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Contact ray.wurlod

Post by ray.wurlod »

I would use BCI functions.
ErrCode = SQLAllocEnv(hEnv)
ErrCode = SQLAllocConnect(hEnv,hDBC)
ErrCode = SQLSetConnectOptions(...) ; * as required
ErrCode = SQLConnect(hDBC,DSN,username,password)
etc.

Search DSXchange for examples about how to use BCI functions.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Top
SURA
Premium Member
Premium Member
Posts: 1229
Joined: Sat Jul 14, 2007 5:16 am
Location: Sydney

Post by SURA »

boppanakrishna wrote:Hi All,

Any more suggestions are welcome

Hi Friend

Right now i started to work with SQL Server, The reason for hanging is -q. Take that and replace with -Q.

simple :)

DS USER
Top
boppanakrishna
Participant
Posts: 106
Joined: Thu Jul 27, 2006 10:05 pm
Location: Mumbai

Post by boppanakrishna »

Hi Sura,

I have used the following code to connect to sql serve rdatabase

Code: Select all

ErrorCode = 0      ;* set this to non-zero to stop the stage/job
$INCLUDE DSINCLUDE JOBCONTROL.H 
Call DSLogInfo("STARTD",DSJ.ME) 
InsertStmt="INSERT INTO DATABASENAME.dbo.T values ('AB');"
Call DSLogInfo(InsertStmt,DSJ.ME) 
SQLSTMT='"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S  SERVERNAME -U USERNAME -P PASSWORD -Q "' : InsertStmt : '"'  
Call DSExecute("NT",SQLSTMT,Output,RtnCode)
Call DSLogInfo(RtnCode,DSJ.ME) 
Call DSLogInfo(Output,DSJ.ME)
The output of the routine is :
Rtncode=1
Output='C:\Program' is not recognized as an internal or external command,
operable program or batch file.

In my current environment, SQL Server , IIS are on different boxes

Is that the possible cause for this?

Is there any environmental variable that needs to be set up to access the database remotely
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Same dang problem as before, the space in the pathname.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
Post Reply

Return to “General”