29
Fri, Nov
0 New Articles

RUNQRY, Run Deep!

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 
Thomas Stockwell

Thomas M. Stockwell is an independent IT analyst and writer. He is the former Editor in Chief of MC Press Online and Midrange Computing magazine and has over 20 years of experience as a programmer, systems engineer, IT director, industry analyst, author, speaker, consultant, and editor.  

 

Tom works from his home in the Napa Valley in California. He can be reached at ITincendiary.com.

 

 

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: