I looked in the manual and could not determine if / how I can use a key file to drive a SQL query WHERE clause to do a key match lookup. Anyone know if this is possible?
Using IDMS 18.5.
Hi Tim -
I did some quick research on this outstanding question of yours. The Culprit documentation in some places mentions tables, but is usually referring to ASF tables, not SQL tables. From what I found, we can not use the KEY/KEYFILE technology with SQL DML, for several reasons. First, Culprit looks up all the information about these keys (for database record, logical records, and tables) in the dictionary; but the information about SQL tables is in the catalog. The other big obstacle is that the network access is iterative - we read one key from the file then issue a DML. For SQL, Culprit issues one request to the SQL engine and the SQL engine does all the database retrieval. It's a very different model of implementation.
Basically, the KEY/KEYFILE technology was not intended to be used with SQL, & was designed long before SQL was available. It has never been updated to support SQL DML. Some architectural changes would have to be made to support this, and if it could be done would have to be implemented a little differently, maybe SQLKEY or something similar. So for now, you will need to manually code the values in the SQL WHERE clause. If you like, you can open a Support issue and I'll be happy to investigate further to get level2 to confirm my findings. Or you can post an idea on the idea wall to request this type of functionality.
I hope this is hlepful, Cal
Principal Support Engineer
Just thinking off the cuff here and having not tried it, what about this.
Use a lower numbered report, say report 0, and read in a flat file with the keys to process in the file.
For each key, construct an entry in a table defined as a global work area (GW).
Then, in a higher numbered report, walk thru the table and cause the SQL command(s) in question to be executed using a variable for the value to be used in the where clause, and, for each iteration, move in a new value from the global table.
Again, I don’t know if this is doable or not, just a thought.
Charles (Chuck) Hardee
Senior Systems Engineer/Database Administration
EAS Information Technology
Thermo Fisher Scientific
300 Industry Drive | Pittsburgh, PA 15275
Phone +1 (724) 517-2633 | Mobile +1 (412) 877-2809 | FAX: +1 (412) 490-9230
Chuck.Hardee@ThermoFisher.com | www.thermofisher.com
WORLDWIDE CONFIDENTIALITY NOTE: Dissemination, distribution or copying of this e-mail or the information herein by anyone other than the intended recipient, or an employee or agent of a system responsible for delivering the message to the intended recipient, is prohibited. If you are not the intended recipient, please inform the sender and delete all copies.
I have given more thought to this and assume I could build a “temporary” table and then do a join to achieve the same result.
First Data USA
(631) 683-6143 – Phone
(631) 897-5317 - Cell Phone
Yes - it's done with a "sub query" - the construct is something along the lines of:
"select .... from .... where selection_column IN ( select sub_selection_column from .... where ..... ) ".
HTH - cheers - GaryC
If you are generating the key-file, you could create it with the "in" syntax.You CULPRIT could have: SELECT .... FROM table WHERE key in (// DD DSN=keyfile,DISP=SHR
// DD *
* 'NONEXISTING KEY' )
Then the key file would have the keys listed as* 'keyvalue',* 'keyvalue',
and it would be read into the culprit in-line during execution.
The idea you posted of the DSN embedded in the IN clause is creative, but it won't work. The SELECT is specified as part of the Culprit syntax which is in SYSIN. The DSN within the IN clause wont be recognized as a DDNAME; it will be interpreted as text. Also, to use a keyfile you need a KEYFILE statement. And as noted earlier, the keyfile can only be used for network records as it works with the PATH, which is also only used in network access. A select is coded instead on an SQL statement.
I consulted our Culprit guru on this question. She was able to supply a value by coding the USE clause and symbolic values. You would have to give a fixed number of values;.she coded this with 1 variable.
=============================== T O P =========================
000001 IN DB(Q)
000002 SQL SELECT EMP_ID FROM DEMOEMPL.EMPLOYEE WHERE EMP_ID > &&1
000003 01510001 EMP_ID
============================ B O T T O M ======================
So this source is stored in CULLIB
It is invoked like this and the 3900 is substituted for &&1
//SYSIN DD *
USE CEPSQL (3900)
I hope this helps. In the future, for faster response from Support about what's possible and what not, you may want to open a Support issue, even if you do so at the same time as posting a thread here.
Best wishes, Cal
Retrieving data ...