IBM AS400 database based on problem tracking database
Bugzero one-for-all problem tracking database
Q: IBM AS400 Database SQLCODE: SQL7008 when updating table

A: The exception is something like:
   java.sql.SQLException: [SQL7008] table_name in database_name not valid for operation.

   The problem is usually a journaling issue. When you use the SQL statement 

   a library is created and a journal is automatically created (QSQJRN) into the library.
   When you subsequently use CREATE TABLE and specify that schema (implicitly or explicitly),
   the created table is automatically journaled to QSQJRN in the schema.

   If you have existing libraries/databases that were created through standard OS/400 commands
   (like CRTLIB or CRTPF), journalling is not enabled by default. You should remove them and
   re-create them using the above SQL statement.

   For information only, you can use the following three steps to journal an OS/400 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)

   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.

* Reference brought to you by Bugzero, it's more than just bug tracking software!

Home - FAQs