• Meaningful Names for Null Indicators

  October 29, 2008 Ted Holt

  There are two ways that a column of a SQL result set can contain the null value. One, it can be derived from an expression that contains a null value. Two, null is returned in outer joins and exception joins when a row in a primary table has no match in a secondary table. In either event, RPG has no way to represent a null value, so what’s a guy (a professional i-on-Power guy, that is) to do when an embedded SQL command might return null values? Since RPG variables cannot be null, SQL loads two-byte (five-digit) integer variables with

  …

  Read more

• Pimp My Font Size, Win a No-Prize

  October 29, 2008 Good-day, Joe

  I have a problem with duplicate 5250 sessions. On our sessions, we change the default screen display font to another font size, such as Courier New. However when we start a second session with the same iSeries Access workstation profile, the screen font reverts back to its default font size: a regular IBM3270 font. What’s going on here and why can’t I use a different font size on multiple sessions? –Jean-Pierre To solve this problem, I went with my default philosophy for debugging computer issues. IF I CAN DUPLICATE IT, I CAN SOLVE IT!!! Unfortunately things don’t always work out

  …

  Read more

• Visual Explain for Run SQL Scripts

  October 22, 2008 Skip Marchesani

  The Run SQL Scripts function, a.k.a. the SQL Script Center or Script Center, allows the user to execute all or a subset of a script that contains one or more SQL statements and/or batch CL commands. It is part of the Navigator Database function and is an extremely powerful and flexible tool with lots of function that can have a very positive impact on application developer productivity. One of these functions is Visual Explain, which is used to investigate and improve the performance of SQL statements. Visual Explain creates a query graph or diagram that graphically displays the execution of

  …

  Read more

• SQL Quickly and Dirtily Extracts a Field from a CSV File

  October 22, 2008 Ted Holt

  A colleague and I were recently tracking the way data progresses through a poorly documented information system. We were trying to determine at which point in the process a certain field’s decimal positions were being discarded. We began at the beginning, which happened to be a CSV file from a PC-based system. How could we quickly determine whether or not the CSV file had values in the decimal positions of the seventh field? We used SQL. I’ll provide a simple illustration. Here are the commands, in case you want to try this yourself. Let’s first create a CSV file we

  …

  Read more

• Healing Failed Windows-i5/OS FTP Transfers

  October 22, 2008 Hey, Joe

  I tried running the attached FTP script to automatically transfer AS/400 Integrated File System (AS/400 IFS) files to a Windows server, but I can’t get it to work. I can run my transfer commands manually but not automatically. Can you tell me what I’m doing wrong? –Dave Thanks, Dave. I’ll give it a shot. Here’s the FTP script that you sent me. OPEN system_name USERNAME PASSWORD CD /ifs_directory_name LCD C:directory_name MGET *.* QUIT Here’s the Windows FTP command that you’re using to run the script. ftp -s:getfile.scr system_name Once you run the command, you get the following FTP error: >

  …

  Read more

• Displaying Multiple Results Sets in Run SQL Scripts

  October 15, 2008 Skip Marchesani

  The SQL Script Center, which is part of the System i Navigator Database function, allows the user to execute a script that contains single or multiple SQL statements. If the script contains multiple SQL statements, the user has the option of executing a single SQL statement, the entire script (starting with first statement in the script and ending with the last statement in the script) or two or more consecutive SQL statements as a subset of the script. The results from executing an SQL statement are shown in the lower portion of the Run SQL Scripts window called the Output

  …

  Read more

• A Practical Way to Add Exports to a Service Program

  October 15, 2008 Ted Holt

  My life was confusing enough already. Then along came IBM with binder language, the CL-like language we use to define the list of exports of a service program. Awhile back I sat down to try to find an effective way to deal with service program exports. Here’s what I came up with. When you create a service program, you must list the exports, that is, the data and/or modules that are available to callers. There is a shortcut. If you specify EXPORT(*ALL) in the Create Service Program (CRTSRVPGM) command, the system will generate the list of exports for you. However,

  …

  Read more

• Admin Alert: Preventing Multiple IPs from Stopping Internet Traffic

  October 15, 2008 Joe Hertvik

  We recently added a second IP interface to one of our i5/OS partitions. After an IPL, the partition stopped talking to Internet-based trading partners. It stopped trading FTP files with outside vendors, it wouldn’t relay email, and it generally stopped talking to the outside world. This week, I’ll look at the common problem that took down our Internet communications and how you can stop it from happening in your shop. A Case Study in Configuring for Multiple IPs After the configuration change, we had two IP interfaces on our partition: one for all our normal IP traffic (10.3.1.55); and a

  …

  Read more

• Want a Fast and Easy Way To Sort Subfile Data?

  October 8, 2008 Susan Gantner

  Note: The code accompanying this article is available for download here. Sorting data is something RPG programs often need to do. If it’s just a simple single field array you’re sorting in order to use the much faster binary search possible with %Lookup, for example, then SORTA works well and is simple. But what if it is a more complex task like sorting the data in a subfile on a user-selected column? Surely you need some more involved techniques, such as retrieving the data from the database again using a different ORDER BY on an SQL SELECT statement or

  …

  Read more

• Testing for Valid Data Representation in SQL

  October 8, 2008 Hey Mike

  How can I tell if a character column contains a valid representation of numeric data? I have a column expression in a Select statement that needs to convert character Column A to numeric only when it contains a valid numeric representation. If Column A doesn’t contain a numeric representation, then I need to convert Column B to numeric. —FHG reader Testing for valid numeric representation is an important task. The problem is that casting character data to numeric will result in a run time error if the data is invalid and therefore it is appropriate to test the data

  …

  Read more

Previous Articles Next Articles