ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

How do i change the library list of qrwtsrvr job

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How do i change the library list of qrwtsrvr job

    Hello,

    I am trying to connect remotely to our prod server from my sql session strsql which i am able to connect to.

    But the library list of the job that is submitted doesn't have the library listed from which i want my file .

    I look at the jobd and its set to *sysval.

    How do i changge it ? Can it be done for my user id only ?
    Can i use CHGPJE) command. to do some setups ?

    I beleive this can be done in i access and though run sqlscripts using jdbc config.Looking for similar thing to do while writing a program.

  • #2
    How are you doing the remote connection in STRSQL? With the connect statement?

    I can think of two ways.

    First, is to not use the connect statement. Instead qualify file names with both library and system name. Example, our dev system is TEST01 and our live system is PROD01.
    I can log in to TEST01, run STRSQL, and enter this query: "select * from PROD01/PRLIB/FILE01"
    As long as all files in the query are prefixed with the same system name, it works. I assume this requires relationships be set up between the two servers, that's beyond my knowledge.
    Note that you may have to change your STRSQL session defaults to ensure it uses a 4-digit year date format like ISO.

    Second is to have a program on the PROD01 server that will set the required libl (a simple CL full of ADDLIBLE's will do). Then from TEST STRSQL, you can run these two commands:
    "connect PROD01" to connect
    "call SOMELIB/LIBLPGM" to set library list for remote job. SOMELIB/LIBLPGM is on PROD01, not TEST01


    There may be other ways that I am not aware of.

    Comment


    • #3
      Originally posted by Vectorspace View Post
      How are you doing the remote connection in STRSQL? With the connect statement?

      I can think of two ways.

      First, is to not use the connect statement. Instead qualify file names with both library and system name. Example, our dev system is TEST01 and our live system is PROD01.
      I can log in to TEST01, run STRSQL, and enter this query: "select * from PROD01/PRLIB/FILE01"
      As long as all files in the query are prefixed with the same system name, it works. I assume this requires relationships be set up between the two servers, that's beyond my knowledge.
      Note that you may have to change your STRSQL session defaults to ensure it uses a 4-digit year date format like ISO.

      Second is to have a program on the PROD01 server that will set the required libl (a simple CL full of ADDLIBLE's will do). Then from TEST STRSQL, you can run these two commands:
      "connect PROD01" to connect
      "call SOMELIB/LIBLPGM" to set library list for remote job. SOMELIB/LIBLPGM is on PROD01, not TEST01


      There may be other ways that I am not aware of.
      Hello vector

      First approach is not working .

      I tried the second one .In order to do that I created a pgm pobject in my lib and tried to call it from another env .However since i don't have my lib in the liblist for the remote job .the call fails with the error

      " External program SOMEPGM in libxx not found".


      I used the same "call SOMELIB/LIBLPGM to do that .but it fails.

      Comment


      • #4
        Sorry, I only just saw your PM. I don't normally think to check for PM's.

        I don't know why Option 1 isn't working. I don't know anything about the behind the scenes setup that makes it work.

        Option 2, can you confirm this is what you have done:
        • Create program SOMELIB/LIBLPGM on server PROD01 (the remote server) that adds required libraries to libl
        • In STRSQL on server TEST01 (the server you want to connect from), run the following SQL statements in order:
          • SET CONNECTION PROD01
          • CONNECT TO PROD01
          • CALL SOMELIB/SOMEPGM
        If that is what you did, then that should have worked as far as I know. If it didn't then I don't know. Best guess, there are missing authorities but I wouldn't know anything about that.

        Just to confirm, the program that you call to set the library list must be on the REMOTE server (the server you are connecting to), not the server you are connecting from. In my example, it must be on PROD01, not TEST01.

        Comment


        • #5
          Originally posted by Vectorspace View Post
          Sorry, I only just saw your PM. I don't normally think to check for PM's.

          I don't know why Option 1 isn't working. I don't know anything about the behind the scenes setup that makes it work.

          Option 2, can you confirm this is what you have done:
          • Create program SOMELIB/LIBLPGM on server PROD01 (the remote server) that adds required libraries to libl
          • In STRSQL on server TEST01 (the server you want to connect from), run the following SQL statements in order:
            • SET CONNECTION PROD01
            • CONNECT TO PROD01
            • CALL SOMELIB/SOMEPGM
          If that is what you did, then that should have worked as far as I know. If it didn't then I don't know. Best guess, there are missing authorities but I wouldn't know anything about that.

          Just to confirm, the program that you call to set the library list must be on the REMOTE server (the server you are connecting to), not the server you are connecting from. In my example, it must be on PROD01, not TEST01.
          Hi yes i have did exactly in this case i am connecting to my dev from uat box .
          However i did not do set connection first
          I directly did
          connect
          followed by call

          Comment


          • #6
            Then I'm afraid I do not know why it's not working.

            Comment


            • #7
              I can only think of one other thing. Can you identify the job on the remote server that is executing the remote SQL? It's job log may give you more information on why it failed to execute the "call somelib/somepgm" command.

              Comment


              • #8
                Originally posted by Vectorspace View Post
                I can only think of one other thing. Can you identify the job on the remote server that is executing the remote SQL? It's job log may give you more information on why it failed to execute the "call somelib/somepgm" command.
                Hi Vector the call is basically a call to a stored proc that i have created with external name as program in my library. and it also fails with program not in library *N.

                I tried with set path = 'lib'
                and then call lib/somepgm()


                SOMEPGM in lib type *N not found


                also on another point i checked the remote job and found that all the library from the jobd are not present in the library for the jobd which is weird and cannot be changed.

                so now i was tryiong to do set schema with the remote library which also fails with file not found

                Comment


                • #9
                  So you created a program to set the library list, created an SQL Stored Procedure to call it, and are calling that SQL Stored Procedure by its external name?
                  if the SQL Stored Procedure does not reference the library list program by library, then it would have to do a library list lookup to find it which would fail as the library list is not set

                  That is not what I meant for you to do anyway. You do not need to create an SQL Stored Procedure. You can just create a simple CL program SOMEPGM that is a list of ADDLIBLE commands, and call that CL program directly with SQL "call SOMELIB/SOMEPGM". No need for SQL Stored Procedures or external names.

                  To summarise:
                  • Create CL Program SOMEPGM
                    • It contains a list of ADDLIBLE's
                  • Compile it as normal for a CL program
                  • Place SOMEPGM into SOMELIB on the Remote system
                  • From STRSQL on the local system:
                    • Connect to remote system
                    • run SQL command:
                      • call SOMELIB/SOMEPGM
                  If it fails:
                  • Post the SQL you used - the connect and the call
                  • Look at the job log of the job on the remote system, copy the errors and post them here

                  Comment

                  Working...
                  X