SQL Job Fails to Set Library List if Current Library is Specified in User Library List #99
Comments
|
NOTE: However, if the "Default SQL schema" also exists in the "Library list", the JDBC connection will not pre-pend the library to the library list. Instead, it will leave the library as positioned in the "Library list". In my solution, I was always pre-pending the current library (the Default SQL schema) and removing it from the library list, if found. So, if you are attempting to replicate the behavior of the JDBC connection, you should only prepend the current library to the library list if it is (1) not the special value of *CRTDFT and (2) only if the current library is not in the library list. |
|
I am assuming this is related to the database extension and not Code for IBM i, so i will transfer this issue to that repo. |
|
Yes. Sorry for miss-reporting it. Thank you for moving it.
Best regards,
Brian
…--
Brian Sleeth
Godsend Consulting
+1 (949) 322-7584
From: barry ***@***.***>
Sent: Wednesday, July 26, 2023 8:13 AM
To: halcyon-tech/vscode-ibmi ***@***.***>
Cc: Brian Sleeth ***@***.***>; Author ***@***.***>
Subject: Re: [halcyon-tech/vscode-ibmi] SQL Job Fails to Set Library List if Current Library is Specified in User Library List (Issue codefori/vscode-db2i#99)
I am assuming this is related to the database extension and not Code for IBM i, so i will transfer this issue to that repo.
—
Reply to this email directly, view it on GitHub <#99> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/ATP4QGIH7LMUICYZS7GSFZTXSEXYBANCNFSM6AAAAAA2YUP74I> .
You are receiving this because you authored the thread. <https://github.com/notifications/beacon/ATP4QGJA636LO244Y5CVTLLXSEXYBA5CNFSM6AAAAAA2YUP74KWGG33NNVSW45C7OR4XAZNMJFZXG5LFINXW23LFNZ2KUY3PNVWWK3TUL5UWJTTCO6VFW.gif> Message ID: ***@***.*** ***@***.***> >
|
|
Are you using system naming or SQL naming? |
|
I am not sure where I would go to check this property. The settings.json for "Code for IBM i" does have the currentLibrary and the libraryList settings. I did not find a property named "naming". So, I assume we are using the default value of "naming": "sql".
…--
Brian Sleeth
Godsend Consulting
+1 (949) 322-7584
From: Jesse Gorzinski ***@***.***>
Sent: Wednesday, July 26, 2023 11:27 AM
To: halcyon-tech/vscode-ibmi ***@***.***>
Cc: Brian Sleeth ***@***.***>; Author ***@***.***>
Subject: Re: [halcyon-tech/vscode-ibmi] SQL Job Fails to Set Library List if Current Library is Specified in User Library List (Issue codefori/vscode-db2i#99)
Are you using system naming or SQL naming?
—
Reply to this email directly, view it on GitHub <#99> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/ATP4QGLGDILY5Q7IMYYVSC3XSFOPDANCNFSM6AAAAAA2YUP74I> .
You are receiving this because you authored the thread. <https://github.com/notifications/beacon/ATP4QGINJ2BHGHHQYGGOD23XSFOPDA5CNFSM6AAAAAA2YUP74KWGG33NNVSW45C7OR4XAZNMJFZXG5LFINXW23LFNZ2KUY3PNVWWK3TUL5UWJTTCPQFUI.gif> Message ID: ***@***.*** ***@***.***> >
|
I should point out that despite my assumption about using SQL naming, if QGPL is the current library and I remove QGPL from the library list, if I check the SQL job, it does establish the library list. If QGPL is the current library and is also present in the library list, the job will have the default library list as configured in system values. I know that with SQL naming, as far as SQL statements themselves are concerned, the library list is not really used. However, if the SQL statement makes a call or triggers a stored procedure that uses (for example) RPG, then the library list will come into play. So, establishing the correct library list is important regardless of which naming method is used. |
|
Hey @BrianGodsend To confirm, did you use the edit button in the SQL Job Manager to manage the settings? When running statements, it will use the selected job in the Job Manager. 
|
|
Thank you for the tip.
I did see the edit settings option. Looking at the settings, the initial library list places the current library list at the front of the list of libraries. This includes the special value *CRTDFT.
I had looked at this before, but, silly me, I did not scroll to the bottom to click the `Apply changes` button.
I removed the *CRTDFT from the first library position and applied the change. I know this will cause the first library in my list of libraries to be treated as the current library. While this changes the behavior of my SQL from how it works in a job stream run on the IBM i, being aware of the difference will keep me alert.
Again, thank you,
Brian
…--
Brian Sleeth
Godsend Consulting
+1 (949) 322-7584
From: barry ***@***.***>
Sent: Thursday, August 3, 2023 8:02 AM
To: halcyon-tech/vscode-db2i ***@***.***>
Cc: Brian Sleeth ***@***.***>; Mention ***@***.***>
Subject: Re: [halcyon-tech/vscode-db2i] SQL Job Fails to Set Library List if Current Library is Specified in User Library List (Issue #99)
Hey @BrianGodsend <https://github.com/BrianGodsend>
To confirm, did you use the edit button in the SQL Job Manager to manage the settings? When running statements, it will use the selected job in the Job Manager.
—
Reply to this email directly, view it on GitHub <#99 (comment)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/ATP4QGK4I2F2APYIA5KN2I3XTO4QDANCNFSM6AAAAAA26NDJUA> .
You are receiving this because you were mentioned. <https://github.com/notifications/beacon/ATP4QGLJ4P4RRWS6BSTAKM3XTO4QDA5CNFSM6AAAAAA26NDJUCWGG33NNVSW45C7OR4XAZNMJFZXG5LFINXW23LFNZ2KUY3PNVWWK3TUL5UWJTTDGD4YW.gif> Message ID: ***@***.*** ***@***.***> >
|
It appears as-if the current library is being pre-pended to the user library list when starting a SQL job. As a result, the setting of the library list fails CPF2184. There are two problems:
The special value of *CRTDFT is being used by the SQL job as-if it were a valid library name; thus causing an error.
If the current library exists in the user portion of the library list, the resulting library list will contain the same library name twice; which is an error.
It should be noted that when the current library is set to a valid library name, the that library is added to the library list between the system (SYS) portion of the library list and the user (USR) portion of the library list. Despite IBM resolving the special value of *CRTDFT to QGPL as needed, IBM does not insert QGPL between the system and user portions of the library list.
Ideally, the setting of the current library should be done by explicitly setting the current library (CHGCURLIB) or the current library should be specified on the CURLIB() parameter of the CHGLIBL command; not included as the first library in the library list.
If the current library cannot be handled in either of the above manners and it must be added to the user library list, then the following guidelines should be considered:
If the current library is *CRTDFT, then do not pre-pend the user library list.
If the current library is valid library name (not the special value *CRTDFT), pre-pend the user library list with the current library name and remove the current library if it is found in the user library list.
Example 1:
If the user library list on the connection is "MYLIB1,MYLIB2,QTEMP" and the current library is set to *CRTDFT, the SQL job will try to establish the library list as "*CRTDFT,MYLIB1,MYLIB2,QTEMP". Because *CRTDFT is not a valid library name, the setting of the library list will fail.
In this example, the library list should be set to "MYLIB1,MYLIB2,QTEMP". The library QGPL should simply not be included in the library list.
Example 2:
If the user library list on the connection is "ALIB1,BLIB2,MYGPL,QTEMP" and the current library is set to MYGPL, the SQL job will try to establish the library list as "MYGPL,ALIB1,BLIB2,MYGPL,QTEMP". Because MYGPL appears twice in the library list, the setting of the library list will fail.
In this example, assuming the current library must be included in the library list, I would expect the library list to be set to "MYGPL,ALIB1,BLIB2,QTEMP".
Active extensions
Remote system
Enabled features
Shell env
Variants
{ "american": "#@$", "local": "#@$" }Errors
[ { "command": "export BUILDLIB=\"QGPL\" && export CURLIB=\"QGPL\" && export USERNAME=\"BSLEETH3\" && export HOME=\"/home/BSLEETH3\" && export LIBLS=\"QHLPSYS IPMDPGM2 IPTSUTL IPWMPGM IPASPGM IPTSPGM IPTSPCH BSLIB IPSAPGM IPSRFIL3 IPDSFIL3 IPDSPGM3 DCXDTALIB DCXPGMLIB DBU11 IPPAFIL3 IPWMFIL3 IPSAFIL3 IPASFIL3 IPTSFIL3 IPTKLNK IPTSCHG033 IPTSPCH033 IPTSMOD3 QGPL QTEMP\" && env", "code": 1, "stderr": "bsh: BUILDLIB=QGPL: is not an identifier", "cwd": "/home/BSLEETH3" }, { "command": "export BUILDLIB=\"QGPL\" && export CURLIB=\"QGPL\" && export USERNAME=\"BSLEETH3\" && export HOME=\"/home/BSLEETH3\" && export LIBLS=\"QHLPSYS IPMDPGM2 IPTSUTL IPWMPGM IPASPGM IPTSPGM IPTSPCH BSLIB IPSAPGM IPSRFIL3 IPDSFIL3 IPDSPGM3 DCXDTALIB DCXPGMLIB DBU11 IPPAFIL3 IPWMFIL3 IPSAFIL3 IPASFIL3 IPTSFIL3 IPTKLNK IPTSCHG033 IPTSPCH033 IPTSMOD3 QGPL QTEMP\" && env", "code": 1, "stderr": "bsh: BUILDLIB=QGPL: is not an identifier", "cwd": "/home/BSLEETH3" }, { "command": "export BUILDLIB=\"QGPL\" && export CURLIB=\"QGPL\" && export USERNAME=\"BSLEETH3\" && export HOME=\"/home/BSLEETH3\" && export LIBLS=\"QHLPSYS IPMDPGM2 IPTSUTL IPWMPGM IPASPGM IPTSPGM IPTSPCH BSLIB IPSAPGM IPSRFIL3 IPDSFIL3 IPDSPGM3 DCXDTALIB DCXPGMLIB DBU11 IPPAFIL3 IPWMFIL3 IPSAFIL3 IPASFIL3 IPTSFIL3 IPTKLNK IPTSCHG033 IPTSPCH033 IPTSMOD3 QGPL QTEMP\" && env", "code": 1, "stderr": "bsh: BUILDLIB=QGPL: is not an identifier", "cwd": "/home/BSLEETH3" } ]The text was updated successfully, but these errors were encountered: