Problems using SQL CONNECT to a remote server within an RPGLE program

75 pts.
Tags:
RPGLE Program
SQL Connect
SQL statements
Let me apologize in advance if I fumble with the terminology and don’t explain myself as clearly as everyone is probably accustomed to. The specific situation I’m dealing with involves the need to have an RPGLE program on the iSeries connect to a data base that resides on a remote server. My boss has set up everything for me so I can connect and run scripts via the System i Navigator tool and it works like a charm. When I try to pull the corresponding SQL statements into my RPGLE program, however, I’m running into connection errors; to be specific, I’m receiving message ID SQ30073 – Distributed Data Management (DDM) parameter value X'119C' not supported. I'm not sure what this means. Can I begin a dialogue that will help me solve this problem I am experiencing with my application program? Thanks so much for your time & help!

Software/Hardware used:
iSeries

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Review the entire text of message ID SQ30073. It's a generic error message that goes on to list various parameter values that cause errors. For x'119C', it says "The SBCS CCSID is not supported." It then says "See previous messages for more information." There may be prior diagnostic messages in the joblog that provide more specific detail about the generic error situation. -- Tom
    125,585 pointsBadges:
    report
  • mikechance812
    TomLiotta, thanks for getting back to me. The messages - neither which I understand - preceding the generic one are the following: CPI9160 Database connection started over TCP/IP or a local socket. CPI9161 Database TCP/IP or local socket connection ended. This probably doesn't help anybody; it certainly doesn't make any sense to me.
    75 pointsBadges:
    report
  • TomLiotta
    What are the OS versions on both ends? What are the DB2 fix levels? If you attempt to do a CONNECT in interactive SQL, does it connect? If you run the RPGLE after running STRDBG, are additional messages available? The indication is that something is being sent in a CCSID that can't translate properly between the two systems. I'd suspect that one of the systems is not configured correctly for data translations across a network. -- Tom
    125,585 pointsBadges:
    report
  • mikechance812
    I had a feeling when I started this there were going to be questions I wouldn't be able to answer. I know my development box is running V7R1M0 and is at DB2 PTF Group SF99701, level 20. I'm being told - and I don't know what any of this means - that the server side is SQL Server 2000 on Windows 2003. We use IBM InfoSphere Federated Server 9.7 on the same server to act as intermediary between SQL Server and iSeries. Here's the Server DB2 fix level info: DB2 administration tools level: Product identifier SQL09075 Level identifier 08060107 Level DB2 v9.7.500.702 Build level s111017 PTF IP23286 When I connect via i Navigator, the SQL CONNECT and SELECT statements work just fine. When I try to run the embedded SQL from my program, I check my joblog and see the 3 errors in this order: CPI9160, CPI9161 and SQ30073. I'm not sure what else I can provide; this is all so foreign to me. Thanks in advance for any & all feedback!
    75 pointsBadges:
    report
  • TomLiotta
    But what happens if you attempt to do a CONNECT in interactive SQL, does it connect? Interactive SQL is accessed through the STRSQL command on your iSeries. . Using iSeries Navigator is a handy test environment, but it won't help much in this case. Your problem is in conversion between SQL Server and the iSeries. When you test with iNav, you're running on your PC, so there is no significant conversion issue. PCs generally talk with PCs okay. But iSeries and mainframes use a different data representation. . You need to do basic testing with STRSQL (interactive SQL) on the iSeries. . What is the iSeries QCCSID system value? . Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    I have added a comment that isn't showing up for some unknown reason. It will probably be released when morning comes for ITKE. -- Tom
    125,585 pointsBadges:
    report
  • mikechance812
    When I use STRSQL, the CONNECT statement does not work and I'm getting the same sequence of errors. CPI9160, CPI9161 and SQ30073. The value of QCCSID is 65535.
    75 pointsBadges:
    report
  • TomLiotta
    A system CCSID of 65535 is not a good idea. It indicates that the system generally expects no translation of data across a network. It is effectively saying that data transfers should be done in binary mode. (That is not very accurate, but it's a good way to think of things when you're first setting up a system.) . Since your system is not well configured for communicating with other systems, you have a couple directions to go. . First thing to note, you can set a useful QCCSID system value. In the U.S.A., the most likely value would be (37). In most cases, it doesn't cause much trouble to make that change, but there are potential considerations that apply. . Before thinking about how to get that done (and it probably should have been done long ago), you need to see how it affects your connections. . Determine first what CCSID value is appropriate. It's almost certain that any of your interactive jobs will have a "default CCSID" assigned by the system that would be appropriate (because (65535) isn't). Use DSPJOB to see what the system thinks is right. Take option 2, 'Display job definition attributes'. Then scroll to the bottom of the definition attributes to see what shows for 'Coded character set identifier' and 'Default coded character set identifier'. . Then run CHGJOB CCSID([defaultCCSID]) using the job's default CCSID to set the actual job CCSID. This will affect only that current job without affecting any of the rest of the system. . After your interactive job has a valid job CCSID, try STRSQL to attempt a remote connection again. Post any different errors back here if they show up. If the previous errors appear again, note that here. We're looking for any underlying problems that might be hidden due to CCSID problems. . Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    For some related extra info, see DB2 9.7 docs for SQL30037N. -- Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following