Sorry, you do not have permission to ask a question, You must login to ask a question.

Sorry, you do not have permission to ask a question.

brainchime.com

brainchime.com

brainchime.com Navigation

  • Home
  • About Us
  • Contact Us

Mobile menu

Close
  • Home
  • Categories
  • Questions
    • New Questions
    • Trending Questions
    • Must read Questions
    • Hot Questions
  • Polls
  • Tags

brainchime.com Latest Questions

Admin
Admin
Asked: October 6, 20242024-10-06T04:03:54+05:30 2024-10-06T04:03:54+05:30In: SQL

How to change STRSQL session to use library list?

Hi, I have set the library DEVLIB on top of the library list by running the command addlible lib(devlib) from the command line, so that file PF1 should get picked from that library as I am running one SQL select query from STRSQL session on IBM i green screen without qualifying the file name with the library name select * from pf1 and getting error PF1 in DEVELOPER type *FILE not found i.e. SQL0204.

I can see that I put the library DEVLIB on the library list and in error i.e. coming on STRSQL session mentions library DEVELOPER which is the same as my user profile name. However, I checked on the machine and there is no library with my user profile created yet, and still, if I create it let’s say then why it’s not picking from the actual library present on top of the library list? Why it is not searching that file in the library list first?

As an alternative, I have to qualify the library name with the file name in the SQL query select * from devlib.pf1 on STRSQL session to get the results which ran fine and gave me the resulting rows.

But again another error caught up here when qualifying the file name with the library name using slash(/) instead of dot(.), the SQL query is select * from devlib/pf1 and got another error while running this query which is Qualified object name PF1 not valid. i.e. SQL5016 cause was mentioned here:

Cause . . . . . : One of the following has occurred:
-- The syntax used for the qualified object name is not valid for the
naming option specified. With system naming, the qualified form of an
object name is schema-name/object-name or schema-name.object-name. With SQL
naming the qualified form of an object name is
authorization-name.object-name.
-- The syntax used for the qualified object name is not allowed.
User-defined types, functions, variables, and sequences cannot be qualified
with the schema name using the / separator in the system naming convention
when used in a query.
Recovery . . . : Do one of the following and try the request again:
-- If you want to use the SQL naming convention, specify the SQL naming
option and qualify the object names in the form
authorization-id.object-name.
-- If you want to use the system naming convention, specify the system
naming option and qualify the object names in the form
schema-name/object-name or schema-name.object-name.
-- With the system naming convention, either make sure the user-defined
types, functions, variables can be found in the current path or use the
period form of naming to qualify the object.

Now here, in this error, it talks about the SQL Naming convention thing which is causing both the errors I think. How to change the session attributes of strsql to search library list namely the Naming convention attribute.

as400db2
  • 0
  • 0
  • 22
  • 7
  • Share
    • Share on Facebook
    • Share on Twitter
    • Share on LinkedIn
    • Share on WhatsApp

Related Questions

  • what is level check error and how to resolve level check error?
  • What are AS400 systems. Please provide a introduction of AS400 systems?
  • Is there any online server available for practicing on AS400 system?
  • What are the menus available in AS400?
  • What is subsystem in AS400?
  • How to copy a save file from IFS to a library?
  • How to transfer savf from as400 to pc?
  • How to copy ifs file to another directory?
  • How to copy savf from ifs to pc?
  • How to copy ifs file to physical file?
  • How to copy save file to ifs?
  • How to copy spool file to ifs?
  • How to copy physical file to ifs?
  • How to copy file from pc to IFS?
  • How to download spool file from AS400?
  • How do I delete a library in AS400?
  • How do I copy data from AS400 to excel?
  • What is library in AS400?
  • How to find all the source physical file available in AS400?
  • How to find all libraries in AS400?
  • How to change the library list in AS400?
  • What is access path in AS400?
  • What is the difference between source physical file and physical file in as400?
  • how to find the source file of an object in as400?
  • how to change record length of source physical file in as400?
  • What is cpf4174 error in as400?
  • What is the use of varying keyword in rpgle?
  • What is DDS in AS400?
  • What is the difference between PF and LF in as400?
  • Why do we use CHGPF command in AS400?
  • how to create physical file in as400?
  • What are the data types supported by physical files in AS400?
  • how to add data in physical file in as400?
  • how to view journal entries in as400?
  • what is the use of ovrdbf in as400?
  • What is an array in AS400?
  • what is a data queue in as400 and why do we use data queue?
  • How to run stored procedure in AS400?
  • How to resolve session and device error in AS400?
  • how to check as400 system values?
  • How to check triggers on a file in as400?
  • How to find damaged objects in AS400?
  • what is module in as400?
  • How to create binding directory in as400?
  • how to create ifs folder in as400?
  • What is ASP in AS400?
  • What is JOBQ and how to create a JOBQ in AS400?
  • What is PSDS in AS400?
  • What is SEU in AS400 and why do we use it?
  • What is the multi-format logical file in AS400?
Leave an answer

Leave an answer
Cancel reply

Browse
Browse

Choose from here the video type.

Put Video ID here: https://www.youtube.com/watch?v=sdUUx5FdySs Ex: "sdUUx5FdySs".

2 Answers

  • Voted
  • Oldest
  • Recent
  • Random
  1. Admin
    Admin
    2024-10-06T04:36:11+05:30Added an answer on October 6, 2024 at 4:36 am

    Looking at both errors SQL0204 and SQL5016 it is clear that the *SQL Naming convention is set as default in your STRSQL session attributes.
    With the *SQL Naming convention being set on your STRSQL session you cannot use the qualifier slash(/) and instead, you have to only rely on using dot(.) as a qualifier. Also, with the *SQL naming convention it will search for the file/table in the schema name same as the user profile on the IBM i system.
    Therefore, both errors SQL0204 i.e. PF in DEVELOPER type *FILE not found, and SQL5016 i.e. Qualified object name PF1 not valid. are valid errors and they are more of a type of Diagnostic error.
    Therefore, to fix this problem use both slash(/) and dot(.) as a qualifier and run SQL queries without the need to qualify file/table with library name and instead search in the set library list in the same session you need to change the STRSQL session attribute namely Naming convention. So, please change the Naming convention attribute from *SQL to *SYS. You can do so by these steps:

    • On STRSQL session press F13 (shift + F1). It will show the following options:
      Interactive SQL Session Services
      
      Select one of the following: 
      
      1. Change session attributes 
      2. Print current session 
      3. Remove all entries from current session 
      4. Save session in source file 
      
      
      
      
      
      
      
      
      
      
      
      Selection 
           1
    • Take option 1 to change session attributes. It will show the following menu:
      Change Session Attributes 
      
      Type choices, press Enter. 
      
      Statement processing . . . . . *RUN *RUN, *VLD, *SYN 
      SELECT output . . . . . . . . 1 1=Display, 2=Printer 
      3=File 
      Commitment control . . . . . . *NONE *NONE, *CHG, *CS, *ALL, *RR 
      *NC, *UR, *RS 
      Date format . . . . . . . . . u *JOB, *USA, *ISO, *EUR, *JIS
      *MDY, *DMY, *YMD, *JUL 
      Date separator . . . . . . . . '/' *JOB, '/', '.', ',', '-' 
      ' ', *BLANK 
      Time format . . . . . . . . . *HMS *HMS, *USA, *ISO 
      *EUR, *JIS 
      Time separator . . . . . . . . ':' *JOB, ':', '.', ',' 
      ' ', *BLANK 
      Data refresh . . . . . . . . . *ALWAYS *ALWAYS, *FORWARD 
      Allow copy data . . . . . . . *YES *YES, *OPTIMIZE, *NO 
      Naming convention . . . . . . *SQL *SYS, *SQL 
    • Change the Naming convention from *SQL to *SYS.
    • Exit from the menu.
    • Exit from the STRSQL session.
    • Now add the library to the library list from the command line using the command ADDLIBLE LIB(DEVLIB). Go back to the STRSQL session and run SQL queries without qualifying the library name with file/table name or even using any qualifier either slash(/) or dot(.) if you are qualifying the file/table name.
      • 0
    • Reply
    • Share
      Share
      • Share on Facebook
      • Share on Twitter
      • Share on LinkedIn
      • Share on WhatsApp
  2. Admin
    Admin
    2024-10-06T04:41:26+05:30Added an answer on October 6, 2024 at 4:41 am

    You can change the Naming convention attribute using various ways at different places.

    • In the Interactive STRSQL environment: Use the key F13(Shift + F1) to change SQL session attributes on STRSQL  session.
    • In Embedded SQL programs: You can use the following code as the first statement before the first executable line in the program.
      EXEX SQL
      SET option naming = *SYS

      or
      EXEX SQL
      SET option naming = *SQL
    • With RUNSQLSTM command: Use the following command
      RUNSQLSTM SRCFILE(libname/srcpf) SRCMBR(srcmbr)
      COMMIT(*none) DFTRDBCOL(libname) DBGVIEW(*source)
      NAMING(*SYS)

      or
      RUNSQLSTM SRCFILE(libname/srcpf) SRCMBR(srcmbr)
      COMMIT(*none) DFTRDBCOL(libname) DBGVIEW(*source)
      NAMING(*SQL)
    • In ACS: define in the connection, Preferences -> Database -> Naming convention -> SQL(*SQL) or System(*SYS)
      • 0
    • Reply
    • Share
      Share
      • Share on Facebook
      • Share on Twitter
      • Share on LinkedIn
      • Share on WhatsApp

Sidebar

Statistics

  • Questions 143
  • Answers 177
  • Comments 0
  • Popular
  • Answers
  • Admin

    Why do we use OVERLAY keyword in data structure subfields ...

    • 12 Answers
  • Admin

    How to call sql stored procedure with output parameter from ...

    • 6 Answers
  • Admin

    How to use declare global temporary table statement in RPGLE?

    • 5 Answers
  • Admin
    Admin added an answer CPF4131 is a record format level check error. This indicates… October 18, 2024 at 1:58 am
  • Admin
    Admin added an answer To open the command prompt with administrator rights you can… October 17, 2024 at 12:27 am
  • Admin
    Admin added an answer In AS400, "AS" stands for Application system. This article discusses… October 13, 2024 at 12:49 pm

Related Questions

  • Admin

    what is level check error and how to resolve level ...

    • 1 Answer
  • Admin

    What are AS400 systems. Please provide a introduction of AS400 ...

    • 1 Answer
  • Admin

    Is there any online server available for practicing on AS400 ...

    • 1 Answer
  • Admin

    What are the menus available in AS400?

    • 1 Answer
  • Admin

    What is subsystem in AS400?

    • 1 Answer

Trending Tags

.htaccess (1) as400 (123) bing-webmaster (2) control-language (12) db2 (33) ftp (8) google-adsense (1) google-search-console (3) https-redirect (1) iasp (4) ifs (22) jar (4) operations (3) php-my-admin (1) qshell (3) robots.txt (4) rpg (26) stored-procedure (3) stroed procedure (1) triggers (1) yoast (4)

Explore

  • Home
  • Categories
  • Questions
    • New Questions
    • Trending Questions
    • Must read Questions
    • Hot Questions
  • Polls
  • Tags

Footer

BrainChime

BrainChime is a blog that posts question-and-answer-based format articles on diverse topics and engages in discussions by allowing people to provide answers/comments without the need to register and log in.

About Us

  • About Us
  • Contact Us

Legal Stuff

  • Terms of Use
  • Privacy Policy
  • Cookie Policy

Help

  • FAQs
  • Categories
  • Tags

© 2024 BrainChime. All Rights Reserved
by BrainChime.

Insert/edit link

Enter the destination URL

Or link to existing content

    No search term specified. Showing recent items. Search or use up and down arrow keys to select an item.