DS_JOBS Query
Moderators: chulett, rschirm, roy
DS_JOBS Query
Hi,
I am executing one query from Routine.
The Query is "SELECT NAME,CATEGORY FROM DS_JOBS WHERE CATEGORY LIKE ='AAB%'"
The output is shown below :
Result = Job name............ Category............
J1AaabXfm AAB\S1Lbw
1 records listed.
I am unable to capture the Category name.
I want to capture both the Job name and the category.
I am executing one query from Routine.
The Query is "SELECT NAME,CATEGORY FROM DS_JOBS WHERE CATEGORY LIKE ='AAB%'"
The output is shown below :
Result = Job name............ Category............
J1AaabXfm AAB\S1Lbw
1 records listed.
I am unable to capture the Category name.
I want to capture both the Job name and the category.
----------------
Rgds,
Anupam
----------------
The future is not something we enter. The future is something we create.
Rgds,
Anupam
----------------
The future is not something we enter. The future is something we create.
What exactly is the question you have; from your captured text output the SELECT command correctly output both your jobname and category name separated by one (or more) spaces.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
I am getting the JobName and Category name in Output when executing the UV command. I want to store these 2 in 2 different variables.
How to separte these 2 fields and equate them to variables ?
How to separte these 2 fields and equate them to variables ?
----------------
Rgds,
Anupam
----------------
The future is not something we enter. The future is something we create.
Rgds,
Anupam
----------------
The future is not something we enter. The future is something we create.
They are separated by a space in your output. Change your query order to "SELECT CATEGORY, JOB FROM DS_JOBS WHERE CATEGORY LIKE ='AAB%" and you'll see the difference.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
If you do
Call DSExecute("UV","SELECT NAME,CATEGORY FROM DS_JOBS WHERE CATEGORY LIKE 'B%'",Res,SystemReturnCode)
You can separate yours lines result with
Res2=Field(Res,@FM,nb)
where nb is your line number
Then you're able to separate into two variables :
Var1=Field(Trim(Res2)," ",1) for the NAME
Var2=Field(Trim(Res2)," ",2) for the CATEGORY
Call DSExecute("UV","SELECT NAME,CATEGORY FROM DS_JOBS WHERE CATEGORY LIKE 'B%'",Res,SystemReturnCode)
You can separate yours lines result with
Res2=Field(Res,@FM,nb)
where nb is your line number
Then you're able to separate into two variables :
Var1=Field(Trim(Res2)," ",1) for the NAME
Var2=Field(Trim(Res2)," ",2) for the CATEGORY
Thibault Hamel
Nanterre - France
Nanterre - France
This is my code. I am still unable to extract the values of Job name and Category.
$INCLUDE DSINCLUDE JOBCONTROL.H
0
Ans=0
JobName='J1AaabXfm'
Command ='SELECT NAME, CATEGORY FROM DS_JOBS WHERE NAME=':SQUOTE(JobName):';'
Call DSExecute('UV', Command, Output, SystemReturnCode)
NoOfMessage = DCount(Output,@FM)
for i=1 to NoOfMessage Step 1
Msg = Field(Output,@FM,i)
If Not(IsNull(Trim(Msg)))
Then
Var1=Field(Msg," ",1)
Var2=Field(Msg," ",2)
End
Next i
Ans=Var1:"***":Var2
$INCLUDE DSINCLUDE JOBCONTROL.H
0
Ans=0
JobName='J1AaabXfm'
Command ='SELECT NAME, CATEGORY FROM DS_JOBS WHERE NAME=':SQUOTE(JobName):';'
Call DSExecute('UV', Command, Output, SystemReturnCode)
NoOfMessage = DCount(Output,@FM)
for i=1 to NoOfMessage Step 1
Msg = Field(Output,@FM,i)
If Not(IsNull(Trim(Msg)))
Then
Var1=Field(Msg," ",1)
Var2=Field(Msg," ",2)
End
Next i
Ans=Var1:"***":Var2
----------------
Rgds,
Anupam
----------------
The future is not something we enter. The future is something we create.
Rgds,
Anupam
----------------
The future is not something we enter. The future is something we create.
If you want job category:
Code: Select all
* -----------------------------------------------------------------
* GetJobCategory(JobName)
* Decription: Returns Job Category.
* Written by: Kim Duke
* Notes:
* -----------------------------------------------------------------
Ans = ''
RoutineName = "GetJobCategory"
Usage = RoutineName:'(JobName)'
* -----------------------------------------------------------------
* initialize variables
* -----------------------------------------------------------------
ErrMsg = ''
DebugSw = @FALSE
* -----------------------------------------------------------------
FileName = 'DS_JOBS'
open FileName to FilePtr else
ErrMsg = 'Can not open ':FileName:' file.'
goto ErrRtn
end
DsJobsPtr = FilePtr
TheJobName = field(JobName, '.', 1)
read JobRec from DsJobsPtr, TheJobName else
ErrMsg = 'No JOB: ':TheJobName
goto ErrRtn
end
JobCategory = JobRec<3>
Ans = JobCategory
goto TheEnd
* ----------------------------------------------------------------------
ErrRtn:
if DebugSw then print
if DebugSw then print ErrMsg
if DebugSw then print
if DebugSw then Ans = ErrMsg
* ----------------------------------------------------------------------
TheEnd:
if DebugSw then print
if DebugSw then print
if DebugSw then print Ans
if DebugSw then print
Mamu Kim
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Easily solved with just four BCI functions
Here's a great opportunity to use BCI functions. The environments for localuv are already set up, available through variables @HENV, @HDBC and @HSTMT. So all you need is one call to SQLExecDirect(), two calls to SQLBindCol() and one call to SQLFetch() (plus appropriate error handling) and you are there!
Please check syntax for SQLBindCol() - that was done from memory!
Code: Select all
SQLStmt = 'SELECT NAME, CATEGORY FROM DS_JOBS WHERE NAME=':SQUOTE(JobName):';'
ErrCode = SQLExecDirect(@HSTMT, SQLStmt)
ErrCode = SQLBindCol(@HSTMT, 1, SQL.B.DEFAULT, Name)
ErrCode = SQLBindCol(@HSTMT, 2, SQL.B.DEFAULT, Category)
ErrCode = SQLFetch(@HSTMT)
ErrCode = SQLFreeEnv(@STMT, SQL.DROP)
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.