RUNQRY, Run Deep!

IBM i (OS/400, i5/OS)
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Have you ever thought about corporate data espionage? To be a good spy, you'd have to get in and get out as quickly as possible. You'd have no idea what tools were available, and very little time to use them. Sound challenging? That's exactly the environment that consultants work in when they arrive at a new client's site. They have to delve through unfamiliar databases just to gain enough information to be productive. If you were placed in that situation as a consultant or a double agent, what basic tool would you use to quickly get information out of the AS/400? RUNQRY, of course! Imagine you've parachuted deep into enemy territory. Your mission (because you chose to accept it, Mr. Phelps!) is to quickly and efficiently gather some specific information from the opponent's database. You've been given a password, but there are only a few hours left before it expires. You've managed to sneak into the machine room in the middle of the night, and now you're sitting in the dark at a terminal hidden behind boxes of greenbar. Your CEO wants to know what weapons a man named James Bond purchased from this company last month. You don't have much time to scramble, no time to program, no opportunity for mistakes. In 10 minutes, a chopper is going to meet you on the roof. At least you're working on an AS/400. You sign on and start poking around a bit with the Work with Libraries (WRKLIB) command. Immediately, you discover a disturbing fact: this is strictly a production machine and there's no time to look for tools! Already you can see there's no PDM, no DBU, not much of anything but a couple of system libraries-one called QQRYLIB, and a single application library called BOMBSRUS. This is going to be tougher than you thought. How are you going to find information about Bond without knowing what tools are at hand? Don't panic! Remember, you're a professional. You've got a few tricks up your sleeve. Fortunately, the AS/400 comes with some sophisticated built-in tools that might just be enough to squeak you by. You add the BOMBSRUS library to your library list using the Add Library List Entry (ADDLIBLE) command. Then you use the Work with Files (WRKF) command and punch in WRKF BOMBSRUS/*ALL to display all the files in the BOMBSRUS library. There's a whole slew of them out there. Unfortunately, there's not a single source file to help you figure things out. What now? Time's ticking away! Paging down through the list of files, you spot a couple of objects that look promising. There's one called DBLAGTMST. This, you surmise, must be the Double Agent Customer Master File. Thank goodness for standardized naming conventions! There's also one called BMBITMMST (the Bomb Item Master?) and, finally, a file called BMBSLSMST (Bomb Sales Master?). You piece together a plan on the spot. Your thinking goes something like this: find Bond's name in the DBLAGTMST file and extract his customer number, then look up his account in the BMBSLSMST file. This should give you a list of the things he's bought. Then, using some sort of item number, poke into the BMBITMMST file to find out exactly what he's purchased. Not a difficult task, normally. But with no tools? So, first things first, you use the Display Physical File Member command and key in DSPPFM DBLAGTMST. How many double agents can there be, right? Lo and behold, you certainly are surprised! There must be 600,000 records in this file. Paging through this will probably take all night! Even using the F16 key to search for the James Bond string will take forever because there's no logical way to spot the beginning or ending of the customer name field. To make matters worse, there's no way to determine which column contains the customer number, which column contains his secret decoder ring serial number, or even- don't you wish-which column contains the telephone number of his voice mail. Everything is a jumble! There's got to be a better way! There is! You type in one simple command: RUNQRY *NONE DBLAGTMST and press the F4 key. This is the secret command that will make everything perfectly clear. RUNQRY is a command shipped with OS/400 that navigates you to your information quickly and efficiently even on a machine that's been stripped of every other piece of software. Despite its name, RUNQRY does not require Query/400 or SQL/400 to operate. It really is the OS/400 secret decoder ring. It works because it's an integrated part of OS/400's built-in DB2/400 database. How powerful is it? If you had time to look at the reference to RUNQRY in the CL reference manual, you'd probably be overwhelmed. The parameter diagram stretches for nearly a page. But, basically, it's pretty simple.

RUNQRY QRY(*NONE) +

QRYFILE(filename)

This will give you a complete Query display or printout. Look at the prompt in 1. It's basic stuff, but it gets the job done. The first parameter identifies the Query that you want to run. Because you don't have a prebuilt Query, you use the *NONE parameter. The next parameter asks for the Query file. Here, you put in the file name that you want to look at-in this case, DBLAGTMST. And since you've already added the BOMBSRUS library to your library list, you can leave the Library parameter set to *LIBL. The Member parameter you leave at *FIRST, although you could use this command to investigate any member within the file. The Report output type identifies where you want to send the resulting query. The default *RUNOPT sends the output to the display, but you could send the output to either *PRINTER or *OUTFILE. Most important for your snooping activities, however, is the Record Selection (RCDSLT) parameter. RCDSLT will allow you to snoop out Bond's customer number. This particular parameter will work only if the Query/400 licensed program is on the system. With Query/400 on the system, you can use RCDSLT to individually select records to ferret out Bond's customer number. Without RCDSLT, you might dump the formatted file to the spooler and search through the printout with the F16 key, but there's not much time to determine if the system has Query/400 hidden somewhere. The fastest way to find out is to try it. With little time to spare, you quickly change the RCDSLT default from *NO to *YES, press the Enter key, and hold your breath. Almost immediately, the AS/400 coughs up the Select Records parameter screen (See 2). You're on a roll! Now all you have to do is select specific records using the field names listed on the bottom of the screen. This certainly beats scanning through a DSPPFM screen dump, trying to identify which field and which column contain the customer name. You quickly scan through the list and find a field called SCTAGTMAN (Secret Agent Man?). Then you tab over to the Test column, where you key in EQ to establish an Equals test. RUNQRY, like all of DB2/400's integrated selection tests (see 3), provides you with a comprehensive set of comparison tests. Finally, you tab the cursor over to the Value column and type in 'JAMES BOND'. You press the Enter key and again you suck in a long breath. This AS/400 grinds and grinds, skimming through 1000...2000...3000 records, displaying each pass at the bottom of the screen. Six hundred thousand records later, it's time for this old slot machine to pay up. It does! A new screen is displayed with Bond's name written all over it. (See 4.) The screen displays all the fields defined in the record, including the headings that were originally set up by the database administrator. You page

This will give you a complete Query display or printout. Look at the prompt in Figure 1. It's basic stuff, but it gets the job done. The first parameter identifies the Query that you want to run. Because you don't have a prebuilt Query, you use the *NONE parameter. The next parameter asks for the Query file. Here, you put in the file name that you want to look at-in this case, DBLAGTMST. And since you've already added the BOMBSRUS library to your library list, you can leave the Library parameter set to *LIBL. The Member parameter you leave at *FIRST, although you could use this command to investigate any member within the file. The Report output type identifies where you want to send the resulting query. The default *RUNOPT sends the output to the display, but you could send the output to either *PRINTER or *OUTFILE. Most important for your snooping activities, however, is the Record Selection (RCDSLT) parameter. RCDSLT will allow you to snoop out Bond's customer number. This particular parameter will work only if the Query/400 licensed program is on the system. With Query/400 on the system, you can use RCDSLT to individually select records to ferret out Bond's customer number. Without RCDSLT, you might dump the formatted file to the spooler and search through the printout with the F16 key, but there's not much time to determine if the system has Query/400 hidden somewhere. The fastest way to find out is to try it. With little time to spare, you quickly change the RCDSLT default from *NO to *YES, press the Enter key, and hold your breath. Almost immediately, the AS/400 coughs up the Select Records parameter screen (See Figure 2). You're on a roll! Now all you have to do is select specific records using the field names listed on the bottom of the screen. This certainly beats scanning through a DSPPFM screen dump, trying to identify which field and which column contain the customer name. You quickly scan through the list and find a field called SCTAGTMAN (Secret Agent Man?). Then you tab over to the Test column, where you key in EQ to establish an Equals test. RUNQRY, like all of DB2/400's integrated selection tests (see Figure 3), provides you with a comprehensive set of comparison tests. Finally, you tab the cursor over to the Value column and type in 'JAMES BOND'. You press the Enter key and again you suck in a long breath. This AS/400 grinds and grinds, skimming through 1000...2000...3000 records, displaying each pass at the bottom of the screen. Six hundred thousand records later, it's time for this old slot machine to pay up. It does! A new screen is displayed with Bond's name written all over it. (See Figure 4.) The screen displays all the fields defined in the record, including the headings that were originally set up by the database administrator. You page

right by hitting the F20 key, snooping out all the information you need (as well as Bond's personal voice mail phone number, just in case you want to pull a little prank later on). You jot down his customer number from a field called SPYNO. His SPYNO is, curiously, 007. You press F3 to cancel the display and you move on to the next file, the one called BMBSLSMST. Using Bond's SPYNO, it doesn't take long to ferret out what items he's purchased in the last couple of months. You just follow the same procedure. Type in RUNQRY *NONE BMBSLSMST and use the RCDSLT parameter testing on SPYNO EQ 007. When you press Enter, RUNQRY coughs up all the items 007 has purchased. The field name in the database is called BOOMER. You discover he's ordered five BOOMERs labeled M16, one BOOMER called AK47, and three BOOMERs dubbed EMC2. Once again, you jot these down and move onto the next file, the one called BMBITMMST. Now time is really running short. You've a rendezvous to make, so you take a chance. When RUNQRY pops up the record selection screen, you key in the following:

 BOOMER EQ 'M16 ' OR BOOMER EQ 'AK47' OR BOOMER EQ 'EMC2' 

Using the OR parameter for each BOOMER should parse out all three records in the BMBITMMST file. Just as you suspected, the AS/400 tells you that the M16 is a rifle, the AK47 is a semiautomatic machine gun, and the EMC2 is a 14 megaton thermonuclear device. You press F3 to cancel, sign off the system, and sneak out through the window. Already you can hear the blades of the chopper whirling in the distance. You take one last look back at the terminal. The green screen burns on in the darkness like a comforting beacon. You look at your wristwatch. It's taken you only 10 minutes from the time you sneaked into the computer room to the time you got the information. No programming was required, just a bit of knowledge and your secret decoder command. RUNQRY cuts to the chase. RUNQRY gets the job done. And RUNQRY runs deep. Thomas M. Stockwell is a senior technical editor for Midrange Computing.


RUNQRY, Run Deep!

Figure 1 The Run Query Prompt

 UNABLE TO REPRODUCE GRAPHICS 
RUNQRY, Run Deep!

Figure 2 The Select Record Prompt

 UNABLE TO REPRODUCE GRAPHICS 
RUNQRY, Run Deep!

Figure 3 RUNQRY Comparison Testing

 Test: Description EQ: Equal. Use this test to determine if the contents of the field and the value are equal. NE: Not equal. Use this test to determine if the contents of the field and the value are not equal. GT: Greater than. Use this test to determine if the contents of the field are greater than the value. LT: Less than. Use this test to determine if the contents of the field are less than the value. GE: Greater than or equal. Use this test to determine if the contents of the field are greater than or equal to the value. LE: Less than or equal. Use this test to determine if the contents of the field are less than or equal to the value. RANGE: Range. Use this test to determine if the contents of the field are within a range that you have specified for the value. LIST: List. Use this test to determine if the contents of the field are equal to one of the values in a list. NLIST: Not List. Use this test to determine if the contents of the field are not equal to any of the values in a list. LIKE: Like. Use this test to determine if the contents of the field have a pattern similar to the value. NLIKE: Not Like. Use this test to determine if the contents of the field have a pattern that is not similar to the value. IS: Is. Use this test to determine if a field is null. The only valid value is NULL or null. ISNOT: Is not. Use this test to determine if the contents of the field are not null. 
RUNQRY, Run Deep!

Figure 4 James Bond's Record in the SCTAGTMAN File

 UNABLE TO REPRODUCE GRAPHICS 
BLOG COMMENTS POWERED BY DISQUS