Perhaps I am the only person who experienced this issue with my "Run SQL scripts", but I am going to share the solution I found.
I have to say "Run SQL scripts" is probably my favorite IBM i tool. I open it when I start my work day, and I use it all day to help me develop and test various SQL statements before I copy them into programs, procedures, or source members that I then create the objects from using the RUNSQLSTM command.
When the latest release of Access Client Solutions, ACS 1.1.8.5, was released I download the new install files, uninstalled the version of ACS I already had, and ran the install program to install it onto my PC. When it completed and I started to use "Run SQL scripts" again I found that I had to qualify the object, file, view, etc. name with the library. If I did not I always received the message telling me that the object was not found.
If you are someone else who is experiencing this too I have the solution!
In "Run SQL scripts"...
1. Select "Connection" on the menu bar.
2. Select "Edit JDBC Configuration – Default" in the drop down list
3. Select the "Format" tab at the top of the "JDBC Configuration – Default" window
4. The default in the "Naming convention" box is "SQL"
You can also run a CL command. Just put CL: before command and don't forget about ; on the end.
ReplyDeletee.g.
cl: call system/setlibl;
I have written about that before.
DeleteYou can also prompt the command with the F4 key.
You can use cl: or you can call a DB2 supplied Stored Procedure QCMDEXC:
DeleteCALL QCMDEXC('put CL command here');
Example: CALL QCMDEXC('CALL CLPGMX (parm1 parm2)';
CALL QCMDEXC('AddLibLE myLib');
RSS tool also allows you to make many different JDBC session setups. This allows you to apply different *LIBL to the session. Then connecting to different library set ups is made easier.
ReplyDeleteGo into the JDBC Configurations... menu option and you get another properties window. Create as many as you need.
-Matt
With a library list applied at the JDBC configuration level, the library list is set each time you connect using that JDBC configuration.
DeleteSwitching libraries is as simple as switching JDBC configuration.
I often use CTRL+F11 to reset my connection for various reasons. Having a JDBC configuration set up that matches my testing environment I can have that library list used right away.
Creating several new JDBC settings at one time can also be rather quick because the JDBC settings are just a text file stored in your "iAccessClient\RunSQLScripts\JDBC" folder.
What default system schema or library list do you have set up in the JDBC connection in Run SQL Scripts? We don't have to qualify it and it works for us.
ReplyDeleteDefault SQL schema: Specifies the default SQL schema. SQL statements can be created without specifying the schema that an object is located in. The objects in the SQL statement are said to be unqualified.
Example: In the following SQL statement, QCUSTCDT is an unqualified object. The schema that is used to locate the table name QCUSTCDT is called the implicit qualifier.
SELECT * FROM QCUSTCDT
Notes:
If a default SQL schema is specified, it will be used as the implicit qualifier. The default SQL schema will also be the first item in the schema list.
If a default SQL schema is not explicitly specified, one of the following applies:
For SQL naming, the implicit qualifier is the run-time authorization. This is a schema with the same name as the user profile specified on the JDBC connection.
For system naming, the implicit qualifier is the job schema list.
Library list: The library list is used for resolving unqualified names. It specifies one or more libraries that you want to add to or replace the library list of the server job. The library names can be separated by spaces or commas. Note that library names cannot be longer than 10 characters in length. You must use the SET PATH statement (SQL) if you have libraries with names longer than 10 characters.
Naming convention: Specifies the naming convention used when referring to objects. The possible values are:
SQL - A period (.) is used as a qualifier between the schema and object name.
SYS - A forward slash (/) is used as a qualifier between the schema and object name.
I don't like using the default schema list because the default schema may not always be the library list you want to work with.
DeleteI think its far better to use cl: or call the IBM supplied stored procedure CALL QCMD('put CL command here');
If anyone has TAATOOLS installed you can also use CHGLBLJOBD to rebuild the session *LIBL. Ex, cl:CHGLBLJOBD MYJOBD;
ReplyDelete-Matt
Is there a way to RETRIEVE the Job# of the JDBC connected job (maybe using RTVJOBA, for example as it is done within a CL-program) or dynamically using SQL table function or some other means?
ReplyDeleteThanks in advance.
You can use the JOB_NAME and then you would need to parse the job number from that.
DeleteThis post will give you an idea of how to do that.
Thank you Simon. Appreciate it very much. I was trying the "Current_Job" just as in Current_Date/Time/Server, and was not getting anywhere.
DeleteJOB_NAME is a Global Variable. See here to learn more about what else you can use.
DeleteHi Simon, Long time follower, I always seem to find myself on your informative site. Question, does your QUSER profile have a JOBD assigned with production library list?
ReplyDeleteI leave the IBM user profiles as they are intended, i.e. default job description, default IBM library list.
Delete