• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1528
  • Last Modified:

Getting SQLCODE -805 in Embedded SQL to remote database

I am getting sqlcode -805  in the "OPEN" sql instruction, when I run this code:
     C/EXEC SQL
     C+ CONNECT TO UWEJD
     C/END-EXEC
     C*
     C/EXEC SQL
     C+ SET CONNECTION UWEJD
     C/END-EXEC
     C*
     C/EXEC SQL
     C+ DECLARE C1 CURSOR FOR
     C+ SELECT * FROM CKTIMEFL
     C+ WHERE TIRTIM BETWEEN :SUNDAY AND :ENDDAT
     C+ ORDER BY TISEC3, TISEC1, TIEMPN
     C/END-EXEC
     C/EXEC SQL
     C+ OPEN C1
     C/END-EXEC
     C                   EXSR      FETCH1
     C                   ENDSR

...When I compile the program, I specify OPTION: *XREF and *SQL,  and DLYPRP= *YES. The name of the remote AS400 is UWEJD. It seems to say that the "Package Name" is not found in the remote system

What Am I doing wrong?
 
0
spr-electrolux
Asked:
spr-electrolux
  • 5
  • 5
1 Solution
 
Gary PattersonVP Technology / Senior Consultant Commented:
When you run the program, do you see message SQL0805 on the job log when it fails?  If so, pull it up and post the extended message description here.

Scan down this article for SQL0805:  http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/rzala/rzalaint.html

Looks like the application is expecting to find and SQLPKG object, either as a result of a remote DRDA connection request, or due to you specifying a particular RDB name when you compiled the program.

- Gary Patterson

Check out my EE profile:  http://www.experts-exchange.com/M_4382324.html
0
 
spr-electroluxAuthor Commented:
Thanks for your prompt reply, Gary...
I am actually not getting any joblog. The program just does not return any data. By the way, this is a CGI program rendering web a page, just in case that information helps.

The only reason I know I am getting SQLCODE = -805 is because I debugged the program. I display the SQLCODE value, right after the SELECT statement and it is 0, then after the OPEN it is -805.

I think you are on the right track, and what i need is to learn about this SQLPKG object. I am not s[pecifying any particular RDB name when compiling. (by the way... what the heck is that?   :-)   )
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
RDB name is "Relational Database Name" and matches entries seen in WRKRDBDIRE command.  These represent remote databases (plus the local database).

Need to figure out what SQLPKG on what system, and why.

Suggest you debug again, and check the job log while debugging after seeing return code -805 for SQL0805.

Does this application only access data locally, or does it access data on remote systems via SQL?

- Gary
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
spr-electroluxAuthor Commented:
I did the WRKRDBDIRE, and found an entry for UWEJD (our remot system), specifying the IP address. By means of a STRSQL session I can do CONNECT TO UWEJD, and the SQL instructions run perfect.

Compiled the program using UWEJD as the RDB name, and I got this:
 SQL1108  10    2065  Column definitions for table CKTIMEFL in *N at UWEJD not
                      found.                                                  
                     Message Summary                                          
   Total     Info   Warning      Error   Severe  Terminal                    
       1        0         1          0        0         0                    
... same results when running....

The system is UWEJD. How do I know what SQLPGK?... I was thinking that the compiler would create this package for me.

Hum... debugging CGI programs can be kind of tricky.... Displaying the data in the web page is about the only way I know, because these programs run only on cycle at a time.

The application access remote data via SQL only.
0
 
tliottaCommented:
> How do I know what SQLPGK?... I was thinking that the compiler would create this package for me.

Just to fill some info, prompt the CRTSQLPKG command and review the extended help. That will give a small basis for further discussion with Gary.

Tom
0
 
spr-electroluxAuthor Commented:
Thank you , Tom

Following your suggestion, I prompted CRTSQLPKG and reviewed the extended help. I see now, the package is created for the specified program, and uses a USER ID and PASSWORD in order to access the data. I have been using *CURRENT, and maybe I need to specify QTMHHTTP, which is the user in a CGI program. What do you think?

Now, how is the system going to know where my file is, being that I cannot specify the library in the embedded SQL? (I get terminal compilation error, saying file cannot be qualified)
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Normally the application will create any required remote SQL Package that doesn't exist.  Sometimes it can't - maybe the connection is read-only, or the profile that it runs under doesn't have rights to create the package.  The extended error information that I hope will will be on the job log with the SQL0805 message will give you more information that will help debug this.

Debugging a native RPG or COBOL program running as a CGI isn't as hard as you might think.  Compile the program with debugging info, and then use STRSRVJOB and STRDBG to debug it just like you would a program running locally in your session.  You'll need to figure out which This link has a little tutorial at the end of how to do it:

http://www.easy400.net/cgidev2o/debug.htm?ts=ts201015154456

- Gary Patterson
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
You need to specify a remote user profile that has rights to perform whatever operations your program needs to perform on the remote table(s)...

Check out the various parameters on the CRTSQLRPI command which allow you to specify all kids of relevant info:

RDB name
Remote credentials
Default collections for dynamic and static sql statements (collection=library)
SQL / System naming convention
etc.

What was the exact compiler message you got when you tried to qualify the table name with a library?

- Gary Patterson


0
 
spr-electroluxAuthor Commented:
The compiler message:
MSG ID  SEV  RECORD  TEXT                                                  
SQL5016  30    2067  Position 23 Qualified object name CKTIMEFL not valid.  
                    Message Summary                                        
  Total     Info   Warning      Error   Severe  Terminal                    
      1        0         0          0        1         0                    

Now, here are the latest news: Your hints helped me to figure that I needed to specify the library for the package to be created in the remote system. After that, the package is being created, and I don't get -805 anymore, but -551, which I understand means authorization issue. I guess I need to submit another question, since I have already given all the authority to the user, so I don't understand.

Thanks for your assistance.
0
 
spr-electroluxAuthor Commented:
After a little more investigatino I was able to find out the problem with the authority. The actual user profle needed was QTMHHTP1. Compiling the program specifying that user to create the package completed the solution. Thanks to Gary and Tom for the great assistance.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Happy to help!

- Gary
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now