|
There is also a special register - there are several, such as CURRENT DATE - that contains the SQL path in force at the time. You can see it using
SELECT CURRENT PATH FROM SYSIBM/SYSDUMMY1 in STRSQL. From the explanation of CURRENT PATH:The initial value of the CURRENT PATH special register in an activation group is established by the first
SQL statement that is executed.- If the first SQL statement in an activation group is executed from an SQL program or SQL package and the SQLPATH parameter was specified on the CRTSQLxxx command, the path is the value specified in the SQLPATH parameter. The SQLPATH value can also be specified using the SET OPTION statement.
- Otherwise,? For SQL naming, QSYS, QSYS2, the value of the authorization ID of the statement
? For system naming, *LIBL There is much more - check it out! The truth is out there! Vern At 05:46 AM 7/27/2005, you wrote:
Hi Alan, My apologies - I did not mean to imply that the problem would be fixed by changing the naming convention. I don't think "true" SQL supports the concept of a library list for unqualified tables or views. You can only have one default library which you specify with SET SCHEMA. SQL does support the concept of a library list for unqualifed stored procedures or UDFs which you specify with SET PATH (but this does not apply to tables or views). Interactive SQL (STRSQL), Run SQL Scripts, embedded SQL and RUNSQLSTM will use the library list if you specify a naming convention of *SYS. You can test this in STRSQL by using F13 to change session attributes (no point in doing it on the STRSQL command if it is retrieving an existing session). For RUNSQLSTM I tried the following simple test. I have a source with UPDATE EMPLOYEE SET COMM = COMM + 0 WHERE EMPNO = '000010'; When I RUNSQLSTM with a naming convention of *SYS it runs just fine and the spool file has the messages SQL7957 0 1 Position 1 1 rows updated in EMPLOYEE in SQLSTAND. SQL7960 0 Commit completed. When I RUNSQLSTM with a naming convention of *SQL it does not run and the spool file has the messages SQL0204 30 1 Position 1 EMPLOYEE in TUOHYP type *FILE not found. SQL7961 0 Rollback completed. Must have been a different error you were getting on the RUNSQLSTM. BTW, RUNSQLSTM does not support SET or DECLARE statements. I think you are hosed when you are using "true" SQL in that you are confined to one schema or you must use qualified names for tables and views. HTH Paul ----- Original Message ----- From: "Alan Campin" <Alan.Campin@xxxxxxx> To: <midrange-l@xxxxxxxxxxxx> Sent: Tuesday, July 26, 2005 11:05 PM Subject: (no subject) > Using RUNSQLSTM and using option *SYS for naming convention. Makes no difference. Tried to use *SQL but same thing. > >> Hi Alan, > > >> My understanding is that PATH is applied when calling an unquailified > >> procedure or UDF. You need to specify a SCHEMA for unquailifed tables or > >> views - which, of course, restricts you to one default library. > > >> The library list is only used if you are using a system naming convention > >> (as on STRSQL, JDBC connection, pre-compiler options) but I don't think you > >> have that option when you are creating a true SQL procedure. > > >> HTH > > >> Paul > > >> ----- Original Message ----- > >>From: "Alan Campin" Alan.Campin@xxxxxxx> > >>To: midrange-l@xxxxxxxxxxxx> > >> Sent: Monday, July 25, 2005 9:59 PM > >> Subject: Library List in SQL Stored Procedures > > > > I am trying to write a SQL Stored Procedure but am having no luck getting > to work. I have written them before and this smells like another complier > bug. > > > > Each time that I try to run this stored procedure, I keep getting a > message saying that everyone of the tables is not found in QGPL if I use > QGPL as the default collection and my library MP1CAMPIA if I do not specify > a default collection. All the libraries are on the library list as well as > being added to Set Path statement but it keeps trying to find them in the > default collection. What gives? This is driving me crazy. If you don't > specify a collection, doesn't it use the library list? > > > > Anyway thanks. > > > > SET DTA_LIBRARY = 'A1' CONCAT INCOMPANYCODE CONCAT 'DTA' ; > > SET AMO_LIBRARY = 'A1AMODTA'; > > > > SET PATH DTA_LIBRARY, AMO_LIBRARY; > > > > SET STMT = 'CRTDUPOBJ OBJ(QAQQINI) FROMLIB(MP1CAMPIA) > > OBJTYPE(*FILE) TOLIB(QTEMP) DATA(*YES) ' ; > > SET STMTLENGTH = LENGTH ( STMT ) ; > > CALL QSYS/QCMDEXC ( STMT , STMTLENGTH ) ; > > > > Update QTEMP/QAQQINI > > SET QQVAL = '*YES' WHERE QQPARM = 'FORCE_JOIN_ORDER' ; > > > > SET STMT = 'CHGQRYA QRYOPTLIB(QTEMP)' ; > > SET STMTLENGTH = LENGTH ( STMT ) ; > > CALL QSYS/QCMDEXC ( STMT , STMTLENGTH ) ; > > > > Sel: Begin > > Declare c1 Cursor With Return For > > Select RFPAYC, > > RFRFNN, > > RFDATE, > > RFAMT, > > CMPCMPY, > > CMPNAME > > From OPMRFPF1 > > Inner Join OPMHDRF1 > > On RFORDN = HDORDN > > Inner JOIN MFMDIVF1 > > On HDODIV = DIVDIV > > Inner JOIN MFMCMPF1 > > On DIVCMPY = CMPCMPY > > Where RFRFNN <> 0 And > > RFDATE Between InFromDate and InToDate > > Order By CMPCMPY; > > Open c1; > > End Sel; -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-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
