Admin Alert: Curing the Access-to-ODBC Blues

by Joe Hertvik

In some shops, it's common to update an OS/400 database by using a Microsoft Access database and an ODBC driver. While this method is relatively simple, it may result in some strange errors that aren't very-well documented. One of the most common Access-to-ODBC errors is the SQL7008 error, which will stop an Access update query dead in its tracks.

I discovered SQL7008 and its solution while using Microsoft Access 2000 and the Client Access ODBC driver packaged with Client Access Express for Windows V5R1. SQL7008 occurs when running this configuration in several different Windows desktop environments, including Windows 2000 and Windows XP.

The SQL7008 error most commonly occurs when you try to update an OS/400 database from Access and the query errors-out with a message that is similar to the following:

Error: [IBM] Client Access Express ODBC (32Bit)
        [DB2/400 SQL] SQL7008 - file in library lib 
        for operation invalid. (#-7008)

The problem is usually a journaling issue. For some OS/400 databases that are created in a collection through SQL, journaling is automatically turned on for databases in that collection. However, if your OS/400 libraries and databases were created through standard OS/400 commands (like CRTLIB or CRTPF), SQL7008 frequently requires you to turn on journaling for the files you want to update through Access. If that's your problem, here's the drill on journaling an OS/400 file to eliminate SQL7008 errors.

OS/400 journaling is a simple procedure that requires you to create a journal receiver and a journal that file-access entries can be written to. There are several steps required in order to activate journaling for a specific file.

Step 1

Create the journal receiver by using the Create Journal Receiver (CRTJRNRCV) command, as follows:

       CRTJRNRCV JRNRCV(lib/jrnrcvname) 
                            THRESHOLD(100000)   
       TEXT('Description text')
      

The receiver will hold the journal entries for your files, and it's important to set a threshold value for your receivers. If you set your THRESHOLD value at 100,000 or above, it makes it easier to set up system change journal management support for the journal that this receiver is attached to. When automatic change support is enabled on a journal, OS/400 automatically creates a new journal receiver and swaps out the old journal receiver when it fills up.

Step 2

Create a journal for your file by using the Create Journal (CRTJRN) command:

CRTJRN JRN(lib/jrnname) JRNRCV(lib/jrnrcvname)
MNGRCV(*SYSTEM) DLTRCV(*YES) TEXT('Description text')

The Journal (JRN) parameter contains the library and name of the journal file you're creating, and Journal Receiver (JRNRCV) specifies the name of the journal receiver you created in step 1. The Manage Receivers (MNGRCV) parameter value of *SYSTEM tells OS/400 to automatically create a new journal receiver and change the journal's current receiver when the current receiver exceeds its allocated storage space, as specified by the THRESHOLD parameter in the CRTJRNRCV command.

The Delete Receivers (DLTRCV) parameter tells OS/400 to automatically delete detached journal receivers as needed. This is an optional parameter that can only be activated when MNGRCV is equal to *SYSTEM, but it's extremely handy in limiting the amount of journal receiver DASD used in your system.

Step 3

Start journaling your physical file by using the Start Journal Physical File (STRJRNPF) command:

STRJRNPF FILE(lib/filename) JRN(lib/jrnname)
          OMTJRNE(*OPNCLO)

The JOURNAL (JRN) parameter contains the name of the journal you created in step 2. You may also want to consider setting the Journal Entries to be Omitted (OMTJRNE) parameter to *OPNCLO, which tells OS/400 not to record file open and close entries in your journal receiver. In a SQL7008 situation where you may be journaling the file only to allow remote updates, this is a handy feature for limiting the size of the journal.

Once journaling is set up for your target update file, your Access query should be able to update the file without producing the dreaded SQL7008 error. According to some Internet documentation, SQL7008 isn't just limited to Access. It can occur in several other applications that use ODBC or JDBC, so keep this technique in mind when any SQL7008 error occurs.

Sponsored By BYTWARE

Job failure... FIXED. Website offline... FIXED. Backups failed...FIXED. Security breach... BLOCKED. All without ever coming in to the office. There isn't a message Bobby can't handle from his PDA; and he's saving his company big bucks on IT staffing. It's the best 5K he ever spent. Find out what Bobby and the rest of the Fortune 100 already know. Get the Message. Get MessengerPlus. www.bytware.com