|
I looked for some of my notes on this... Not quite the way I remembered.... http://www-912.ibm.com/n_dir/nas4apar.nsf/c79815e083182fec862564c00079d117 The basis for my original research was a failure with CPYTOIMPF from a specific member of a file in QTEMP. The CPYTOIMPF is implemented as a stored procedure, and is invoked by a SQLCLI call to DB2. Because SQL doen't play well with members, the function creates an alias entry in the DB catalog, but the system filtered out QTEMP alias entries, so SQL couldn't find the data by its alias. Error. It's a stretch, but in my mind, it seemed likely a problem with adding records to the DB-Xref files that point to non-global addresses. Files in a job's QTEMP are not global, so the system doesn't want to register them to SQL. None of this seems to apply at V5. Eric DeLong Sally Beauty Company MIS-Project Manager (BSG) 940-898-7863 or ext. 1863 -----Original Message----- From: Rusling, John B. (Alliance) [mailto:jbrusling@xxxxxxxxxxxxxxx] Sent: Friday, April 25, 2003 8:12 AM To: 'rpg400-l@xxxxxxxxxxxx' Subject: RE: Creating a file in qtemp with a unique key field using embedd ed sql. Thanks for replying Eric and Rob; We are on Release 5.1. I use QTEMP and get what I want using, CREATE TABLE, CREATE INDEX but it's TWO steps. I hope it's changed for Release 5.2. I'd prefer a ONE-step solution. John B. Message: 4 Date: Thu, 24 Apr 2003 15:52:38 -0500 From: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx> Subject: RE: Creating a file in qtemp with a unique key field using embedd ed s ql. John, I ran across this originally in V4R4. This seems to relate to the SQL system catalog, which is supposed to be the global directory of file objects on the system. The problem is that the system won't add an entry to the system file catalog if the file exists in QTEMP, because QTEMP cannot be referred to globally. I thought I saw that this was changed in V5, so that if QTEMP is part of the pathname to a file, the system would bypass the system catalog lookup code and access the file directly. The real problem is the use of QTEMP. Either use another library that exists globally, or leave off the Primary key constraint. Eric DeLong Sally Beauty Company MIS-Project Manager (BSG) 940-898-7863 or ext. 1863 -----Original Message----- From: Rusling, John B. (Alliance) [mailto:jbrusling@xxxxxxxxxxxxxxx] Sent: Thursday, April 24, 2003 3:28 PM To: 'rpg400-l@xxxxxxxxxxxx' Subject: Creating a file in qtemp with a unique key field using embedded s ql. I'm trying to create a file in qtemp with a unique key field using embedded sql from an interactive rpg program. The code thats giving me a problem is - C EVAL SqlString2= 'CREATE TABLE' C + ' qtemp/dollibs' C + ' (' C + 'dlLib CHAR ( 10)' C + ' NOT NULL WITH DEFAULT,' C + ' PRIMARY KEY(dlLib)' C + ' )' C/Exec SQL C+ PREPARE PP1 FROM :SqlString2 C/End-Exec C/Exec SQL C+ EXECUTE PP1 C/End-Exec What happens is the file doesns't get created. Checking the session job log gives me this message - Constraint is not valid. press F1 on the message yields - *--------------------------------------------------------------------------- ------* Additional Message Information Message ID . . . . . . : CPD32B0 Date sent . . . . . . : 04/24/03 Time sent . . . . . . : 15:11:51 Message . . . . : Constraint is not valid. Cause . . . . . : Constraint *N cannot be added for file DOLLIBS in library QTEMP for TYPE value *N. For a referential constraint (TYPE *REFCST), the parent file *N in library *N has a delete rule of *N and update rule of *N. The constraint was not added because of errors. The reason code is 15. The reason codes and their meanings are as follows: 00 - See previous messages. 01 - Constraint name not valid. 02 - TYPE value not valid. 03 - DLTRULE value not valid. 04 - UPDRULE value not valid. 05 - File is damaged. 06 - File constraints are logically damaged. 07 - System Cross Reference files are damaged. 08 - A primary key constraint already exists. 09 - A duplicate unique constraint exists. 10 - The KEY length is too long. 11 - The parent file does not exist. 12 - The parent key does not exist. 13 - The file can have a maximum of one member. 14 - The file cannot be a source file. 15 - The file cannot be in the QTEMP library. 16 - The parent and dependent files must be in the same ASP. 17 - The file does not allow write, update, or delete operations 18 - The file must be an externally described file. 19 - The file must not be a distributed file. Recovery . . . : Do one of the following based on the reason code shown, and then try the request again. 01 - Correct the constraint name and retry the function. 02 - Correct the TYPE value and retry the function. 03 - Correct the DLTRULE value and retry the function. 04 - Correct the UPDRULE value and retry the function. 05 - Delete (DLTF) the file. 06 - Remove (RMVPFCST) the damaged constraints. 07 - Perform a reclaim storage (RCLSTG). 08 - Remove the primary key constraint (RMVPFCST) and then try again. 09 - Remove the unique constraint (RMVPFCST) and then try again. 10 - Choose fields whose combined length does not exceed the maximum length allowed. See the Database User's Guide. 11 - Create (CRTPF) the parent file. 12 - Add primary key or unique constraint (ADDPFCST) to parent file. 13 - Change the file (CHGPF) to maximum members of one. 14 - Use a file other than a source file. 15 - Use a library other than the QTEMP library. 16 - Use files in the same ASP. 17 - Use a file that allows write, update and delete operations. 18 - Use an externally described file. 19 - Use a file which is not a distributed file. Technical description . . . . . . . . : X'00000000000000000000' Bottom Press Enter to continue. F1=Help F3=Exit F6=Print F9=Display message details F12=Cancel F21=Select assistance level *--------------------------------------------------------------------------- ------* Does anyone know why this cannot be done, or if it can be done and how ? I can do it if a create a non-keyed file in qtemp and then create a logical over it in qtemp. (using embedded sql, CREATE TABLE, CREATE INDEX) Thanks for any help. John B. _______________________________________________ This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo.cgi/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l. _______________________________________________ This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo.cgi/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
