Re: CREATE PROCEDURE fails with SQL system error -- MIDRANGE-L

Try this - it has always worked for me (and I have seen this over and over
again on V4R4, in fact I can't think of a V4R4 machine on which it has
*not* happened).


******

The following is 99% certain to be the problem. Basically, the latest cum
PTF package messes up DB2 (it's to do with turning DB2 into UDB).

APAR#: II12248
Component: INFOAS400 - AS/400 Information
Release(s): R440

Abstract
OSP-XTND UDB EnhancePak may not convert some system files.


Error Description
"Last Updated 21-March-00"

On August 3rd, 1999, IBM announced details of DB2 Universal
Database for AS/400, features that were previewed in the
February 1999, Version 4 Release 4 (V4R4) announcements.
DB2 UDB support for large object, and user-defined types and
functions will come on September 24, 1999.  This additional
function is being delivered via the DB2 UDB Group PTF (also
being called EnhancePak.)  For more details of the
announcement, see URL;
http://www.as400.ibm.com/db2/v4r4annc.htm
Two additional delivery methods are now
available.  Cumulative PTF tape C99313440
and later have the enhanced function.
Install media from IBM dated 02/02/00 or
later have the enhanced function.

Unfortunately, users of stored procedures have encountered
unpredictable results after applying the EnhancePak to systems
that did not already have the enhanced function.  These results
are caused by incomplete file conversion. The files are not user
files.  The unpredictable results are limited to SQL functions.

This information APAR will describe
1. how to avoid unpredictable results
2. how to check if the files are correct
3. how to determine if the UDB EnhancePak
  has been integrated into the
  operating system
This information APAR will also
1. show some of the unpredictable results and
2. show how to recover from those results.

AVOID UNPREDICTABLE RESULTS.

There are two ways to avoid these problems.
1. use install media dated 02/02/00 or after.  The files
  on this media do not need to be converted.
2. create a Distribution tape from a system that has the
  files at the correct level.
  (GO LICPGM option 40)

CHECK FILES.

For each of the listed files, type the command DSPFD and
press enter.  At the next screen, type Format on the Find
line and press F16.  You should see "Format Level
Identifier" on the right hand side of the screen.
Check the Format Level Identifier to see that it matches
the one listed below.  If it does not, or if the file is
not on the system, you will need to perform the recovery
steps.

The files are in QSYS2 library.

FILE        FORMAT LEVEL IDENTIFIER
SYSROUTINE  5348ECAD7B0A8
QASQRESL    453176E75A98A
SYSPROCS    42F34FEBA6F89
PROCEDURES  3586292F5DFAA
SYSFUNCS    39F6B34FD3293
These format identifiers only apply to English systems.

** N.B. you need the RECORD FORMAT level identifier - check carefully that
you have the right one. Also, PROCEDURES probably won't be there at all. **

DETERMINE IF INTEGRATED INTO OS/400.

There are three main ways to tell if the EnhancePak function
has been integrated into the operating system.

1.  Type the command DSPPTF and press enter.  The screen
   that shows up next should have a list of PTF's.  If
   the top item is RE99352, then it is integrated.
2.  If you have cumulative tape C99313440 or later applied,
   then it is integrated.
3.  Type the command DSPDTAARA SF99104 and press enter.
   If the dataarea shows SF99104-4 or later, then
   it is integrated.

LIST OF UNPREDICTABLE RESULTS.

SQL commands such as CREATE PROCEDURE, DROP PROCEDURE,
or CALL may end with one or more of the following
messages issued to the joblog.

SQL0204, MCH0601, SQL0901,
SQL0444, MCH1210, CPF5035 RC14, and
CPF5035 RC20 for file SYSROUTINE.

Some of the IBM programs connected with these messages are
QSQPROC, QSQCALL, and  QQQSVRTN.

RECOVERY PROCESS.

If your system has SF60184 applied and any of the files listed
above are not at the correct format level, then follow these
steps to recover.

1. Signon as QSECOFR, type CHGJOB LOG(4 00 *SECLVL) and
  press enter.

2. Make sure the five files are not locked by any jobs.
  Locks can be checked by issuing the command;
  WRKOBJLCK lib_name/file_name *FILE MBR(*ALL)
  If there is a lock on the file, end the job with the lock.
  If the job name is SCPF, then an IPL will be needed to
  release that lock.  Once all the locks are released, you
  are ready to continue with step 3.

3. Type CALL QSYS/QDBFIXS2 and press enter.
  Wait for a completion message as follows.
  'Special handling program completed successfully.'
  This is message identifier CPC1214 from program QDBFIXS2
  to program QUIMNDRV.

  If you get any other message, review the cause and recovery
  and take appropriate action.  If the message is CPF3202,
  use the command
  WRKOBJLCK lib_name/file_name *FILE MBR(*ALL) to find the
  job that has a lock on the file.

  Once all messages have been resolved, rerun the
  CALL QSYS/QDBFIXS2 command.

Problem Summary


Problem Conclusion

Temporary Fix


Comments


Circumvention


PTFs Available


Affected Modules


Affected Publications


Summary Information
Status................. CLOSED  CAN
HIPER.................. No
Component..............
Failing Module.........
Reported Release....... R440
Duplicate Of...........

IBM disclaims all warranties, whether express or implied, including, but
not limited to, the implied warranties of merchantability and fitness for a
particular purpose. By furnishing this document, IBM grants no licenses to
any related patents or copyrights. Copyright (c) 1994, 1995, 1996,1997,1998
IBM Corporation. Any trademarks and product or brand names referenced in
this document are the property of their respective owners. Consult your
product manuals for complete trademark information.


                        ...........................






Benjamin Budai <BENJI@ludens.elte.hu> on 20/01/2001 19:51:47

Please respond to MIDRANGE-L@midrange.com

To:   midrange-l@midrange.com
cc:    (bcc: Mandy Shaw/Pacific/UK)
Subject:  CREATE PROCEDURE fails with SQL system error




Hi all,

I was playing around with UDB, and I tried to create SQL stored procedure
from the interactive SQL shell. The CREATE PROCEDURE fails. OS/400 sends
CPF5035 with reason code 20 for fields DBINFO, PARALLEL, FINAL_CALL,
SCRATCHPAD,
NULL_CALL and EXT_ACTION of the catalog table QSYS2/SYSROUTINE.
Seems that DB2 tries to insert a record without a default value for these
fields, and the fields don't allow null. I experienced this on a model 170
(2291) running V4R4. Today I got the same result on a 2290, V4R4 scratch
install
 + CUM C0252440. Looked up the PTF database on the web, but found nothing.
Has anybody run into this problem already?

Benjamin Budai
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to
MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---



      Regards,
      Mandy Shaw

      Catalyst Solutions plc
      Kingfisher House
      Frimley Business Park
      Camberley
      Surrey
      GU16 5SG
      UK

      http://www.catalyst-solutions.com
      Email: Mandy.Shaw@uk.catalyst-solutions.com

      Telephone: +44 (0)870 166 1000
      DDI: +44 870 166 1324
      Facsimile: +44 870 168 3920
      Mobile: +44 410 447966





----------------------------------------------------------------------
----------------------------------------------------------------------
Catalyst Solutions plc.  Registered No 2918101.
Registered @ Kingfisher House, Frimley Business Park, Frimley,
Surrey. GU16 5SG   U.K.

NOTICE:
This message is intended only for the named addressee(s) and may
contain confidential and/or privileged information. If you are not the
named addressee you should not disseminate, copy or take any action
or place any reliance on it. If you have received this message in error
please notify postmaster@catalyst-solutions.com and delete the message
and any attachments accompanying it immediately.
----------------------------------------------------------------------


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---