|
View previous topic :: View next topic
|
| Author |
Message |
Manas Sinha
New User
Joined: 29 Apr 2008
Posts: 20
Location: United Kingdom
|
|
|
|
Hi All,
I want to write a REXX which searches a table for a particular value in one column and returns a value in that row. The table will look something like this -
5011202 3
5416789 2
And so on....
So if we give 5011202, it should return 3 as output.
Also how can I make this REXX available globally to all TSO Users ?
Amit. |
|
| Back to top |
|
 |
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
Is the table indexed on the variable name that you wish to search ?
If so, look at TBSCAN
To share the REXX, place it in a SYSEXEC / SYSPROC defined library common to all users. |
|
| Back to top |
|
|
purusothaman
New User
Joined: 17 Feb 2007
Posts: 39
Location: Chennai
|
|
|
|
Hi,
Try with this and let me know if it works for you.
| Code: |
/* CONNECT TO DB2 SUB SYSTEM */
ADDRESS DSNREXX 'CONNECT YYYY'
ADDRESS TSO "STEPLIB DA('YYYY.SDSNEXIT','YYYY.SDSNLOAD') SHR"
ADDRESS SQL
ADDRESS TSO "SUBCOM DSNREXX"
IF RC THEN
DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
/* SELECT QUERY TO GET THE DETAILS */
S1 = "SELECT ID FROM YYYY.TABLE_NAME",
" WHERE NAME = JAMES ",
" FOR FETCH ONLY WITH UR"
SQLSTMT = S1
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
ADDRESS DSNREXX "EXECSQL OPEN C1"
ADDRESS DSNREXX "EXECSQL FETCH C1 INTO :PKEY "
SAY PKEY
ADDRESS DSNREXX 'DISCONNECT' |
YYYY - DB2 system to be connected |
|
| Back to top |
|
|
Manas Sinha
New User
Joined: 29 Apr 2008
Posts: 20
Location: United Kingdom
|
|
|
|
Apologies for not clarifying this earlier.
The I/P table would be a sequential (PS) file that I would create.
Best regards,
Amit. |
|
| Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
| Manas Sinha wrote: |
| The I/P table would be a sequential (PS) file that I would create. |
Please clarify, a PS file or an ISPF table built from the PS file ?
Because if it is a straight PS file, you will need to read it sequentially. |
|
| Back to top |
|
|
Manas Sinha
New User
Joined: 29 Apr 2008
Posts: 20
Location: United Kingdom
|
|
|
|
It would be a PS file and since it would be a small file, sequential read will not be a problem.
Still I would be interested in knowing if there is a better way to do it ?
Best regards,
Amit. |
|
| Back to top |
|
|
ofer71
Global Moderator
Joined: 27 Dec 2005
Posts: 2358
Location: Israel
|
|
|
|
You can use a binary search (make sure the data is sorted), like in the following example:
| Code: |
/****************************** REXX ******************************** */
/* */
/* Name.......: BINISRCH */
/* */
/* Function...: Itterative binary search. */
/* */
/* Date.......: 13/12/2004. */
/* */
/* Author.....: OFER */
/* */
/* Reqirements: - */
/* */
/* Description: Search a sorted array by repeatedly dividing the */
/* search interval in half. */
/* Complexity: 0(log n). */
/* */
/* INPUT: Supply your array in DATA. */
/* Make sure that DATA.0 contains the number of */
/* elements in the array (filled automatically by */
/* EXECIO). */
/* */
/**********************************************************************/
ADDRESS TSO "ALLOC FI(SAMPDATA) DA('your.input.dataset') SHR"
"EXECIO * DISKR SAMPDATA (STEM DATA. FINIS"
"FREE FI(SAMPDATA)"
SAY BIN_SEARCH('your_target')
RETURN
/*------------------------------------------------------------------*/
BIN_SEARCH:
/*------------------------------------------------------------------*/
ARG TARGET
TOP = DATA.0
BOT = 1
FOUND = 0
DO WHILE (FOUND = 0 & TOP >= BOT)
MID = (TOP + BOT) % 2
IF TARGET = DATA.MID THEN
FOUND = 1
ELSE
IF TARGET < DATA.MID THEN
TOP = MID - 1
ELSE
BOT = MID + 1
END
RETURN MID |
O. |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
If the (same) values have to be searched many times,
it is worth investing in loading a stem table the following way:
| Code: |
| "EXECIO * DISKR TBCONV (FINIS TB1." |
After this we have TB1.1 = "5011202 3", TB1.2 = "5416789 2" and so on.
| Code: |
TB2. = ""
Do Ix = 1 To TB1.0
Parse Var TB1.Ix F1 F2
TB2.F1 = F2
End |
After this, we have a new stem table with TB2.5011202 = 3, TB2.5416789 = 2 and so on.
Now, replacing one value with the other is easy, no search needed:
| Code: |
| NewCode = TB2.OldCode |
NB: it also works with characters, not only with numbers |
|
| Back to top |
|
|
|
|
