Faq

Deleting Duplicate Records From A TableIf you have SQL, this is possible in a few steps as long as you have fields you can use to determine what is a duplicate. These fields would be the fields you want to be unique in your file. Assuming your file is named MyFile, start by creating a grouping view that includes just the key fields and a field for COUNT(*); use a HAVING clause such as HAVING COUNT(*) > 1. Name the view MyFileGV. This view now shows every set of keys that are duplicated (or triplicated or more). Next, create an inner join view over MyFile and MyFileGV. Include the key fields from MyFile and a field for RRN(MyFile). Name the new field RRNfld. Since the original table is joined to the table of duplicate keys, you now have a view that shows all the record numbers for each duplicate. Name this view MyFileJV. Next, create a grouping view over MyFileJV that includes the key fields and a field for MAX(RRNfld). This view will provide you with the highest RRN() for any of the duplicates. So far, all you've done is create three new views. The last view tells you exactly which record numbers to delete. A DELETE statement with a WHERE clause can then be based on this last view to delete specific record numbers. The first time you run through the DELETE, the highest record numbers for each duplicate will be deleted and the temporary views will all readjust automatically. The second time you run through it, records that were originally triplicated will be deleted. And so forth for higher multiples. When there are no more records to delete, the temporary views will be empty.data queues and how to useCan anyone that actually know, explain to me what are data queues, what the benefit of using them are, and how to use them. An IBM book number would greatly be appreciated? I am considering using them in a system we are writing at work to get our AS400 to dial out on an asynchronous line to Transunion for credit checks. This program will be accessed by a number of Service Reps. and I am hoping that a Data Queue Guru will shed some light on the in's and out's. Data Queues are a cross between data areas, and message queues. They are a method for asynchronous communication between programs. A typical use for a data queue is to have a job sitting in a batch subsystem waiting for a data queue entry to be created, and multiple programs dropping entries into the data queue. The ERP system my company uses has a single process to print invoices which is triggered by entries from multiple order entry staff to the data queue. It sounds like your application fits the bill for using data queues. There are API programs to read and write to data queues, and they are quite straight-forward to use. If memory serves, they are QSNDDTAQ and QRCVDTAQ, and they are well documented in the book, although I don't know the number. If you like I can send you examples. Benefits: - performance can be dramatically improved over individual submits if the job is complex - record locking conflicts are eliminated if only one job is updating. - they can facilitate clean modular design Drawbacks - they are hard to document well - the next programmer will have to think to figure them out - they can't really be audited, backed up, or for that matter conveniently examined. - the contents are almost invisible, although smart programmers have written program to read the queue, print the entry, and re-write it. - Once the entry is read it is gone; if the program halts the entry is lost. This can be gotten around

with an audit file; write a record when the entry is written, nad have the receiver program update a status field when done. Also, data queues don't support data definition, so you do need to use data structures if you intend to pass more than a single data element. In the example above, the data queue holds the order to be invoiced as well as the output queue to place the spooled file and the user to notify when it is printed. Explaining them to a 'data queue beginner' is maybe easiest by comparing them to other objects to see similarities and differences. Then you can get into purpose and useability. A data queue is similar to a database file that has records written to it. One program (or many programs) can send entries to the queue. Each entry is similar to a record. Another program (or many programs) can read entries back from the queue, similar to reading records. Differences to begin with are in formats (record descriptions), reading the same entries more than once and speed. An entry on a data queue has no external description; it's just a string of bytes. If you want something like "fields", you'll have to do all the concatenating and sub-stringing yourself. Normally, an entry is read only once. When the entry is read off the queue, it is gone. The first program to read the entry gets it and then it's gone. (It's possible to get around this, but there's seldom a reason to.) Data queues are designed to provide fast communication between programs. You might have a dozen programs feeding entries onto a queue and a single program receiving those entries. The entries might represent transactions that you want performed against your database and you don't want those dozen programs all doing it individually. You centralize the process in the receiver program. The time it takes for an entry to be sent from one program and be received by another is minimal, less than if you used a file to hold records. Alternatively, you might have one program feeding entries as fast as it can onto a queue and have a dozen programs receiving entries. By having the transactions processed by a dozen programs, you can multiply the work being done. And since each entry is removed from the queue when it's received, you don't have to worry about another program getting the same entry. The speed is partially achieved by eliminating any overhead done by the system. An example is the way the system handles the space used by a data queue as entries are added and removed. If you start a program up to add entries to the queue but there's no program started to receive the entries, the allocated space gets bigger. When the entries are later received and removed from the queue, the space allocated does _not_ get smaller. You must delete and recreate the data queue to recover excess space if want it back. This means you must know the original parameters used to create the *DTAQ object so you can recreate one to match. (There's an API to get this info that you can get into later.) If you prefer, you can think of a *dtaq as being similar to a message queue. You can send messages from one program and another can receive them from the *msgq. If you do a RCVMSG RMV(*YES), the message is gone from the *msgq, similar to how an entry is removed from a *dtaq. And a *dtaq entry has a format similar to a message; i.e., there's no format except what you create yourself. (Note that MSGDTA() can be used to provide some general formatting with a message.) Entries are generally sent by calling the QSNDDTAQ API and received by calling the QRCVDTAQ API. One handy use for me is in CL programs where you're limited to a single file declaration. If

you use these APIs, you can use any number of *dtaqs to simulate physical files, either for passing info from one part of a program to another or for passing to a different program(s). Perhaps start by creating a *dtaq with CRTDTAQ and writing a program to send some entries to it. Then do a DMPOBJ and examine the output. Then write a second program to receive the entries and do a second DMPOBJ. Testing it out can be done with some pretty small CLPs. Data queue APIs are technically described for Version 4 in the OS/400 Object APIs manual on the Systems Programming Support Bookshelf. The data queue is a very simple concept. In your application you would have a single job that handles credit checks. When a credit check is needed, the program talking to the service rep sends a message to the data queue for the credit check job. This "wakes up" the waiting credit check job and it proceeds to dial and do the credit check. When it's done it sends a message back to a data queue for the requesting job, waking that job back up and giving it the results of the check. You can do various things, like have the credit check job check the incoming data queue for new messages needing processing before hanging up the line after completing a credit check. Just use a "dequeue but don't wait" operation in this case, vs the usual "dequeue with wait" operation. For some reason queues are rarely used (or provided as primitives) in computer systems, even though they are one of the most efficient and easiest to manage mechanisms for synchronizing multi-threaded applications. Retrieving SMTP NameI want to create a cgi][ program (in RPG IV) to create a dynamic page of all users' e-mail addresses. I haven't been able to find an API (or command that output to a file) to retrieve SMTP names. I can use WRKNAMSMTP to display them on screen and DSPDIRE to and outfile to retrieve proper name information, but no SMTP name. Anyone point me in a direction?Since you put up an ILE RPG example, I thought a CLP would be nice to compare against. Having more or less the same thing in two radically different languages using differing techniques might make the API easier to figure out. It isn't one of the nicer APIs to make work on your own. The CLP can be found at: http://zap.to/tl400 under Files/View my files. The CLP accepts a system distribution directory user ID and address and returns the associated SMTP address. It does not include any handling of the API's error code; it's just a program I put together for my own use. You can find all defined SMTP names in QUSRSYS/QATMSMTPA.RPGLE example for Dynamic Screen Manager APIThere is an ILE C example for the Dynamic Screen Manager API in the book but we need one in ILE RPG - If someone has done this yet or knows where to find on - please let me know.I once got this sample from someone, it's definitely a good start. * * Bind with *SRVPGM QSNAPI *D F3 c x'33'D sa_norm c x'20'D txt s 128 inz('Press Enter to Roll, F3.')D txtlen s 9b 0 inz(32)D err s 8 inz(x'0000000000000000')D aid s 1D lines s 9b 0 inz(1)D wf1 s 1D wrtn s 9b 0D ClrScr PR 9b 0 extproc('QsnClrScr')D mode 1 options(*nopass) constD cmdbuf 9b 0 options(*nopass) constD env 9b 0 options(*nopass) constD error 8 options(*nopass)D WrtDta PR 9b 0 extproc('QsnWrtDta')D data 128D datalen 9b 0D fldid 9b 0 options(*nopass) constD row 9b 0 options(*nopass) constD col 9b 0 options(*nopass) constD strmatr 1 options(*nopass) constD endmatr 1 options(*nopass) constD strcatr 1 options(*nopass) constD endcatr 1 options(*nopass) constD cmdbuf 9b 0 options(*nopass) constD env 9b 0 options(*nopass) constD error 8 options(*nopass)D GetAID PR 1 extproc('QsnGetAID')D aid 1 options(*nopass)D env 9b 0 options(*nopass) constD error 8 options(*nopass)D RollUp PR 9b 0 extproc('QsnRollUp')D lines 9b 0 constD top 9b 0 constD bottom 9b 0 constD cmdbuf 9b 0 options(*nopass) constD env 9b 0 options(*nopass)

constD error 8 options(*nopass)C Eval wrtn = ClrScr('0' : 0 : 0 : err)C DoW wrtn = 0C Eval wrtn = WrtDta (txt : txtlen : 0 : 23 : 2 :C sa_norm : sa_norm : sa_norm : sa_norm :C 0 : 0 : err)C Eval wf1 = GetAID (aid : 0 : err)C If aid = F3C LeaveC EndIfC Eval wrtn = RollUp (lines : 1 : 24 : 0 : 0: err)C EndDoC SetOn LrC ReturnCalling APIs from CL - with examples!/*-------------------------------------------------------------------*//* LSTMBRC - List processing for file members - Example Pgm *//* *//* By Craig Rutledge, 1/14/95, Version 1.0 *//* *//* This program was done as an example of working with APIs in *//* a CL program. *//* *//*-------------------------------------------------------------------*//* Program Summary: *//* *//* Initialize binary values *//* Create user space (API CALL) *//* Load user space with member names (API CALL) *//* Extract entries from user space (API CALL) *//* Loop until all entries have been processed *//* *//*-------------------------------------------------------------------*//* API (application program interfaces) used: *//* *//* QUSCRTUS create user space *//* QUSLMBR list file members *//* QUSRTVUS retrieve user space *//* See SYSTEM PROGRAMMER'S INTERFACE REFERENCE for API detail. *//* *//*-------------------------------------------------------------------*/ PGM/*-------------------------------------------------------------------*//* $POSIT - binary fields to control calls to APIs. *//* #START - get initial offset, # of elements, length of element. *//*-------------------------------------------------------------------*/ DCL &$START *CHAR 4 /* $POSIT */ DCL &$LENGT *CHAR 4 /* $POSIT */ DCL &#START *CHAR 16 DCL &#OFSET *DEC (7 0) DCL &#ELEMS *DEC (7 0) DCL &#LENGT *DEC (7 0)/*-------------------------------------------------------------------*//* Error return code parameter for the APIs *//*-------------------------------------------------------------------*/ DCL &$DSERR *CHAR 256 DCL &$BYTPV *CHAR 4 DCL &$BYTAV *CHAR 4 DCL &$MSGID *CHAR 7 DCL &$RESVD *CHAR 1 DCL &$EXDTA *CHAR 240/*-------------------------------------------------------------------*//* Define the fields used by the create user space API. *//*-------------------------------------------------------------------*/ DCL &$SPACE *CHAR 20 ('LSTOBJR QTEMP ') DCL &$EXTEN *CHAR 10 ('TEST') DCL &$INIT *CHAR 1 (X'00') DCL &$AUTHT *CHAR 10 ('*ALL') DCL &$APITX *CHAR 50 DCL &$REPLA *CHAR 10 ('*NO')/*-------------------------------------------------------------------*//* various other fields *//*-------------------------------------------------------------------*/ DCL &$FORNM *CHAR 8 ('MBRL0200') /* QUSLMBR */ DCL &$FIELD *CHAR 30 /* QUSRTVUS */ DCL &$MEMBR *CHAR 10 DCL &$FILLB *CHAR 20 ('QDDSSRC JCRCMDS ') DCL &$MBRNM *CHAR 10 ('*ALL ') DCL &$MTYPE *CHAR 10 DCL &COUNT *DEC (5 0)/*-------------------------------------------------------------------*//* Initialize Binary fields and build error return code variable *//*-------------------------------------------------------------------*/ CHGVAR %BIN(&$START) 0 CHGVAR %BIN(&$LENGT) 50000 CHGVAR %BIN(&$BYTPV) 8 CHGVAR %BIN(&$BYTAV) 0 CHGVAR &$DSERR + ( &$BYTPV || &$BYTAV || &$MSGID || &$RESVD || &$EXDTA)/*-- Create user space. ---------------------------------------------*/ CALL PGM(QUSCRTUS) PARM(&$SPACE &$EXTEN &$INIT + &$LENGT &$AUTHT &$APITX &$REPLA &$DSERR)/*-------------------------------------------------------------------*//* Call API to load the member names to the user space. *//*------------------------------------------------------------------*/ A: CALL PGM(QUSLMBR) PARM(&$SPACE &$FORNM &$FILLB + &$MBRNM '0' &$DSERR) CHGVAR %BIN(&$START) 125 CHGVAR %BIN(&$LENGT) 16/*-------------------------------------------------------------------*//* Call API to return the starting position of the first block, the *//* length of each data block, and the number of blocks are returned. *//*-------------------------------------------------------------------*/ CALL PGM(QUSRTVUS) PARM(&$SPACE &$START &$LENGT + &#START &$DSERR) CHGVAR &#ELEMS %BIN(&#START 9 4) /* # OF ENTRIES */ IF (&#ELEMS = 0) GOTO C /* NO OBJECTS

*/ CHGVAR &#OFSET %BIN(&#START 1 4) /* TO 1ST OFFSET */ CHGVAR &#LENGT %BIN(&#START 13 4) /* LEN OF ENTRIES */ CHGVAR %BIN(&$START) (&#OFSET + 1) CHGVAR %BIN(&$LENGT) &#LENGT/*-------------------------------------------------------------------*//* Call API to retrieve the data from the user space. &#ELEMS *//* is the number of data blocks to retrieve. Each block contains a *//* the name of a member. *//*-------------------------------------------------------------------*/ CHGVAR &COUNT 0 B: CHGVAR &COUNT (&COUNT + 1) IF (&COUNT *LE &#ELEMS) DO CALL PGM(QUSRTVUS) PARM(&$SPACE &$START &$LENGT + &$FIELD &$DSERR) CHGVAR &$MTYPE %SST(&$FIELD 11 10) /* MEMBER TYPE */ CHGVAR &$MBRNM %SST(&$FIELD 1 10) /* EXTRACT MEMBER NAME */ IF (&$MTYPE = 'PRTF ') DO /* ANZPRTFF PRTF(&$MBRNM) SRCFILE(JCRCMDS/QDDSSRC) */ ENDDO CHGVAR &#OFSET %BIN(&$START) CHGVAR %BIN(&$START) (&#OFSET + &#LENGT) GOTO B ENDDO C: ENDPGM /*-------------------------------------------------------------------*//* LSTOBJC - List processing for OBJECTS - Example Pgm *//* *//* By Craig Rutledge, 1/03/94, Version 1.0 *//* *//* This program was done as an example of working with APIs in *//* a CL program. *//* *//*-------------------------------------------------------------------*//* Program Summary: *//* *//* Initialize binary values *//* Create user space (API CALL) *//* Load user space with object names (API CALL) *//* Extract entries from user space (API CALL) *//* Loop until all entries have been processed *//* *//*-------------------------------------------------------------------*//* API (application program interfaces) used: *//* *//* QUSCRTUS create user space *//* QUSLOBJ list objects *//* QUSRTVUS retrieve user space *//* See SYSTEM PROGRAMMER'S INTERFACE REFERENCE for API detail. *//* *//*-------------------------------------------------------------------*/ PGM/*-------------------------------------------------------------------*//* $POSIT - binary fields to control calls to APIs. *//* #START - get initial offset, # of elements, length of element. *//*-------------------------------------------------------------------*/ DCL &$START *CHAR 4 /* $POSIT */ DCL &$LENGT *CHAR 4 /* $POSIT */ DCL &#START *CHAR 16 DCL &#OFSET *DEC (5 0) DCL &#ELEMS *DEC (5 0) DCL &#LENGT *DEC (5 0)/*-------------------------------------------------------------------*//* Error return code parameter for the APIs *//*-------------------------------------------------------------------*/ DCL &$DSERR *CHAR 256 DCL &$BYTPV *CHAR 4 DCL &$BYTAV *CHAR 4 DCL &$MSGID *CHAR 7 DCL &$RESVD *CHAR 1 DCL &$EXDTA *CHAR 240/*-------------------------------------------------------------------*//* Define the fields used by the create user space API. *//*-------------------------------------------------------------------*/ DCL &$SPACE *CHAR 20 ('LSTOBJR QTEMP ') DCL &$EXTEN *CHAR 10 ('TEST') DCL &$INIT *CHAR 1 (X'00') DCL &$AUTHT *CHAR 10 ('*ALL') DCL &$APITX *CHAR 50 DCL &$REPLA *CHAR 10 ('*NO')/*-------------------------------------------------------------------*//* various other fields *//*-------------------------------------------------------------------*/ DCL &$FORNM *CHAR 8 ('OBJL0100') /* QUSLOBJ */ DCL &$FIELD *CHAR 30 /* QUSRTVUS */ DCL &$DEVNM *CHAR 10 /* RMT002P */ DCL &$OBJLB *CHAR 20 ('*ALL QSYS ') DCL &$OBJTY *CHAR 10 ('*LIB ') DCL &COUNT *DEC (5 0)/*-------------------------------------------------------------------*//* Initialize Binary fields and build error return code variable *//*-------------------------------------------------------------------*/ CHGVAR %BIN(&$START) 0 CHGVAR %BIN(&$LENGT) 5000 CHGVAR %BIN(&$BYTPV) 8 CHGVAR %BIN(&$BYTAV) 0 CHGVAR &$DSERR + ( &$BYTPV || &$BYTAV || &$MSGID || &$RESVD || &$EXDTA)/*-- Create user space. ---------------------------------------------*/ CALL PGM(QUSCRTUS) PARM(&$SPACE &$EXTEN &$INIT + &$LENGT &$AUTHT &$APITX &$REPLA &$DSERR)/*-------------------------------------------------------------------*//* Call API to load the object names to the user space. *//*-------------------------------------------------------------------*/ A: CALL PGM(QUSLOBJ) PARM(&$SPACE &$FORNM &$OBJLB + &$OBJTY &$DSERR)

CHGVAR %BIN(&$START) 125 CHGVAR %BIN(&$LENGT) 16/*-------------------------------------------------------------------*//* Call API to return the starting position of the first block, the *//* length of each data block, and the number of blocks are returned. *//*-------------------------------------------------------------------*/ CALL PGM(QUSRTVUS) PARM(&$SPACE &$START &$LENGT + &#START &$DSERR) CHGVAR &#ELEMS %BIN(&#START 9 4) /* # OF ENTRIES */ IF (&#ELEMS = 0) GOTO C /* NO OBJECTS */ CHGVAR &#OFSET %BIN(&#START 1 4) /* TO 1ST OFFSET */ CHGVAR &#LENGT %BIN(&#START 13 4) /* LEN OF ENTRIES */ CHGVAR %BIN(&$START) (&#OFSET + 1) CHGVAR %BIN(&$LENGT) &#LENGT/*-------------------------------------------------------------------*//* Call API to retrieve the data from the user space. &#ELEMS *//* is the number of data blocks to retrieve. Each block contains a *//* the name of a object and information about that object. *//*-------------------------------------------------------------------*/ CHGVAR &COUNT 0 B: CHGVAR &COUNT (&COUNT + 1) IF (&COUNT *LE &#ELEMS) DO CALL PGM(QUSRTVUS) PARM(&$SPACE &$START &$LENGT + &$FIELD &$DSERR) CHGVAR &$DEVNM %SST(&$FIELD 1 10) /* EXTRACT DEVICE NAME */ /* INSERT CODE HERE */ CHGVAR &#OFSET %BIN(&$START) CHGVAR %BIN(&$START) (&#OFSET + &#LENGT) GOTO B ENDDO C: ENDPGM/ Compare two strings in RPG character by characterNow I want to compare two strings letter by letter using RPG.Example: 'America' and 'American' Who knows any function I can implement this(except %scan)? Answer(s):I'm not sure what you want to achieve, but to compare two fields character-by-character you could use the following code: (sample definitions)D FIELD1 S 999D FIELD2 S 999D LENGTH S 3 0 INZ(%SIZE(FIELD1))D INDEX S + 2 LIKE(LENGTH)C 1 DO LENGTH INDEXC IF %SUBST(FIELD1:INDEX:1) =C %SUBST(FIELD2:INDEX:1)...(your code - you might want to leave the loop using the LEAVE opcode)...C ENDIFC ENDDO Why can't you just compare them? IF Field1=Field2? If Field1 is 7 char long and Field2 is 8 char long, RPG will test with the greatest length padded with blanks so you will test : "America " (with a blank) = "American" and that's false. See http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/QB3AGZ00/4.3.6. How about If Trim(Field1) = Substr(Field2,Len(Field1)) Then Shouldn't it be If Trim(Field1) = Substr(Field2,Len(trim(Field1))) ?? But they don't match... with or without the blank...so I don't see the point. Unless you want If Str1 = %SubSt(Str2:1:%Len(%Trim(Str1)) you could use the c function strcmp D strcmp PR 10I 0 ExtProc('strncmp')D s1 1000 ValueD s2 1000 Value To use this you would have to terminate the string with nulls C Eval rc = strcmp(s1+x'00':s2+x'00) But using strcmp gives the exact same answer as comparing the strings in RPG, only in a slower and more complicated way. By the way, Mike, your prototype for strcmp won't work. > D strcmp PR 10I 0 ExtProc('strncmp') > D s1 1000 Value > D s2 1000 Value It should be one of these (two ways to fix the parameters + two different functions (strcmp takes 2 parms, strncmp takes 3) D strcmp PR 10I 0 ExtProc('strcmp') D s1 1000 Const D s2 1000 Const D strcmp PR 10I 0 ExtProc('strcmp') D s1 * Value options(*string) D s2 * Value options(*string)OPTIONS(*STRING) isn't strictly necessary. D strcmp PR 10I 0 ExtProc('strncmp') D s1 1000 Const D s2 1000 Const D len 10u 0 Value D strcmp PR 10I 0

ExtProc('strncmp') D s1 * Value options(*string) D s2 * Value options(*string) D len 10u 0 ValueHow to search all pgms in QCLSRC for a keywordI have to change TAP03 to TAP01 in every CL. To avoid forgetting one, I'm looking for a tool to search all members of QCLSRC and list where it occurs. Anybody knows one?Use option 25 against the file in PDM. This gives you the find string screen. You can set the option in this screen to 2 to bring up any matched source in edit mode. I cant remember, but I think that if you set up your replace options before using this, you can just do replace once the source is displayed. You can sbmjob the following command: FNDSTRPDM STRING(TAP03) FILE(Your_source_lib/QCLSRC) MBR(*ALL) OPTION(*NONE)PRTMBRLIST(*YES) And before doing this, create a data area to store the device name in. Then when you locate every occurrence in every CL, change the CLs to get the device from the data area rather than hard-coding it into the programs. Next time you have to change the device name, just change the data area.Using Multiformat Logical To Join 2 Identical FileI have two identical files except for their object names. One is a current production file, the other is a history file with last years data. Of course, I need a program that use these two files as one. But, I can't seem to get a Join Logical to compile. Keeps running into duplicate field and key field names. Is there anyway to do this?Answer(s): Don't try to use Join Logical file, use Multiple Record Logical files. The funny thing is, this is a vendor file that used to be multiple formats for each year but in their Y2K version they changed it. Why? Beats me! Any ways, sorry if I am a little dense here. Could you give me an example of two identical files using the same logical with mulitple records? Here an example of a multiformat logical file : A R FORMAT1 PFILE(FILE1)A FIELD1A FIELD2....A K FIELD1A*A R FORMAT2 PFILE(FILE2)A FIELD1A FIELD2....A K FIELD1 In the program, you can chain with the file name and you'll get records from both physical files, or the format name and you'll get records only from the specifc PFILE. If you want to update or write a record with this logical file, you must use the format name. Classification: GE Public