• Admin Alert: Turning Off ODBC Query Timeout Limits

  July 13, 2005 Joe Hertvik

  OS/400 ODBC processing is notorious for obscure error messages that offer little information about their causes or their solutions. One of the strangest is the SQL0666 error, which stops SQL query processing when the estimated query run time exceeds the system’s query processing limit. While others have documented how to avoid this error by changing an ADO property, newer iSeries Access ODBC drivers allow you to disable query timeouts directly within your Data Sources (DSN).

  For those who have not run into it, the SQL0666 error usually happens when you are using ODBC to process a large query from your

  …

  Read more

• API Corner: TCP/IP List Network Interfaces to a Browser

  June 29, 2005 Shannon O'Donnell

  The code for this article is available for download.

  Welcome to a new feature of Four Hundred Guru called API Corner, where I will mine the mountain of APIs in OS/400 to find the gems that you can use. This week, I will be taking a look at the List TCP/IP Network Interfaces API. This API allows you to programmatically list the network interfaces on your system and retrieve certain information about same.

  

  For example, some of the information you can retrieve includes the IP address of the network interface as well as its name and type. To make

  …

  Read more

• Remove Misleading Messages from Job Logs,

  June 29, 2005 Hey, Ted

  
  Take Two

  I agree that removing handled messages from the job log is very helpful. One optimization would be to put the API call in its own procedure, as that API has many parameters. Because we are adding a call level, we need to adjust the CallStackCtr parameter to 1 instead of 0. That is, we want to remove the message sent to the caller of our procedure.

  

  prmvExceptionMsg  b                   export
  d                 pi
  
  d ApiErr          ds
  d  provided                     10u 0 inz(%size(ApiErr))
  d  available                    10u 0 inz  
  d MsgInfo         s              8a
  /free
      QMHRCVPM(MsgInfo: %size(MsgInfo): 'RCVM0100': '*': 1: '*EXCP'
               : *blanks : 

  …

  Read more

• Admin Alert: Three iSeries Access Data Transfer Download Tricks

  June 29, 2005 Joe Hertvik

  The Data Transfer from iSeries program, which allows you to download OS/400 data for display, printing, or to a PC file, is one of the more valuable iSeries Access for Windows features. While the program’s interface is fairly easy to understand and run, it’s also valuable to use the following three tricks to solve common problems with running the program and to make data transfers easier to run for your users.

  

  Tip #1 What to do if you’re unable to locate the file you want to download in the Data Transfer from iSeries Browse function. This problem occurs when

  …

  Read more

• Execute SQL Statements on DB2 UDB for Windows from the iSeries

  June 22, 2005 Michael Sansoterra

  With the necessity of tight integration between platforms pervading many IT shops, I wondered what interoperability exists between the DB2 Universal Database (UDB) on the iSeries and DB2 UDB for Windows. After a little investigation, I was pleased to find that DB2 SQL on the iSeries can be used to execute SQL statements on the Windows DB2 platform as easy as it can on a different iSeries using IBM’s Distributed Relational Database Architecture (DRDA).

  

  For a simple intro to DRDA using two iSeries systems or partitions, see Using DRDA to Execute SQL Statements on Another iSeries. For in-depth info

  …

  Read more

• Case-Insensitive Sorting and Record Selection with Query/400

  June 22, 2005 Hey, Ted

  Thanks for explaining how to ignore case when sorting and selecting records with SQL. Can you tell me how to do the same with Query/400?

  –Ron

  

  Sure. First, here’s a database file that we can query.

  LASTNAME    FIRSTNAME 
  smith       billy     
  Smith       Amos      
  SMITH       CHARLEY   
  smith       DICK      
  Smith       ELMO      
  SMITH       dan       
  JONES       Andy
  

  The normal sort places lowercase letters before uppercase ones, like this:

  LASTNAME    FIRSTNAME 
  smith       billy     
  smith       DICK      
  JONES       Andy      
  Smith       Amos      
  Smith       ELMO      
  SMITH       dan       
  SMITH       CHARLEY
  

  From the Define the Query panel, place a 1 (one) beside the Select collating sequence option and press Enter. If your

  …

  Read more

• Turning Off ODBC Journaling Is Not a Good Idea

  June 22, 2005 Hey, Joe

  After reading your column on “Curing the ODBC Blues,” I came to the same conclusion that you did regarding how to avoid the SQL7008 error: You need to journal your OS/400 files to avoid ODBC update errors. But then someone showed me the ODBC advanced server option in the iSeries Access ODBC driver, where you can set your ODBC drivers’ Commit mode to eliminate the need for journaling files updated by ODBC. This seems like a better solution.

  –Brock

  

  Here’s my take on the situation.

  By default, IBM sets iSeries Access for Windows ODBC drivers up with a Commit mode

  …

  Read more

• Query Active Directory from the iSeries

  June 15, 2005 Michael Sansoterra

  The code for this article is available for download.

  With respect to iSeries programs, real time integration with other platforms and databases has been one of my favorite pursuits. Unfortunately, as a consultant, I perceive that many have left the iSeries because of the perception that certain things could not be done programmatically. Of course, with the addition of Java and the APIs available to the ILE programmer, we know that the iSeries can do about anything–sometimes you just have to dig a little to find out how. In this article, I’m going to discuss a technique to query

  …

  Read more

• Fetch a Variable Number of Records with SQL

  June 15, 2005 Hey, Ted

  If you want to use a variable for the number of rows to fetch in an SQL query, there is an alternative to the FETCH FIRST n ROWS technique you presented recently in Four Hundred Guru. Here is some code from an SQLRPGLE program shell I have used since V5R1. It uses a cursor instead of placing the number of rows directly into the SELECT statement.

  c/Exec SQL                                         
  c+ Fetch from C1 for :NbrRows rows into :RtnDtaSet 
  c/End-Exec
  

  –Rick

  

  Rick’s comment is in response to the last paragraph of the issue he refers to. His idea is to use

  …

  Read more

• Admin Alert: A Quick Audit of Your IPL Parameters

  June 15, 2005 Joe Hertvik

  Being a mature operating system, i5/OS V5R3 (as well as several earlier versions of the OS/400 operating system) contains a number of system values that determine when, where, and how your system can be IPLed. As such, it’s worth performing a mini-IPL audit every once in a while just to familiarize yourself with the system’s IPL options as well as to insure that your IPL configuration meets your shop’s needs.

  

  Inside i5/OS, IBM allows you to configure rules about your IPL strategy through a number of IPL-related system values. On the green screen, these values can be located and modified

  …

  Read more

Previous Articles Next Articles