Connect/Reset Connect from within Native Stored Procedure

Stefan Kolev

Connect/Reset Connect from within Native Stored Procedure

We have been approached by a development team that would like to issue Connect/Reset Connect from withing a Native (SQL PL) Stored Procedure.  I have not found any documentation either way.  Has anyone tried and failed or succeeded at this?  Our attempts so far have been fruitless as the Contoken on the remote DB2 does not match the one on the local DB2 after Bind Deploy.  Any ideas would be appreciated!

Stefan Kolev | Database Services | |D|S|T| SS&C

 

James Campbell

Connect/Reset Connect from within Native Stored Procedure
(in response to Stefan Kolev)
I haven't tried this, but can you just BIND COPY the SP's package to the remote system?

James Campbell




On 2 Oct 2019 at 12:03, Stefan Kolev wrote:

> We have been approached by a development team that would like to issue Connect/Reset Connect from withing a Native (SQL PL) Stored Procedure.  I have not found any documentation either way.  Has anyone tried and failed or succeeded at this?  Our attempts so far have been fruitless as the Contoken on the remote DB2 does not match the one on the local DB2 after Bind Deploy.  Any ideas would be appreciated!
>
> Stefan Kolev | Database Services | |D|S|T| SS&C
>  
>


--
This email has been checked for viruses by AVG.
https://www.avg.com

Stefan Kolev

RE: Connect/Reset Connect from within Native Stored Procedure
(in response to James Campbell)

Thank you, James!

We tried that yesterday and the contoken now matches.  We just need to get past the -525 we are getting when trying to execute a statement on the remote DB2.

 

Stefan Kolev | Database Services | |D|S|T| SS&C

Stefan Kolev

RE: Connect/Reset Connect from within Native Stored Procedure
(in response to James Campbell)

We are able to CONNECT TO location-name as long as it is hard coded.  Every time I have tried using a host-variable I keep getting -950.  Now I know the location name exists and I am populating the :hv with a valid value.

The manual lists these requirements for CONNECT TO:

TO location-name or host-variableIdentifies the server by the specified location name or by the location name that is contained in the host variable. If a host variable is specified:

  • It must be a CHAR or VARCHAR variable with a length attribute that is not greater than 16. (A C NUL-terminated character string can be up to 17 bytes long.)
  • It must not be followed by an indicator variable.
  • The location name must be left-justified within the host variable and must conform to the rules for forming an ordinary identifier.
  • If the length of the location name is less than the length of the host variable, it must be padded on the right with blanks.
  • It must not contain lowercase characters.
  • If used with an SQL procedure language application, host variable must be a qualified SQL-variable name or a qualified SQL-parameter name.

Has anyone successfully used a host variable for the location name when CONNECTing from a Native Stored Procedure?

 

 

Stefan Kolev | Database Services | |D|S|T| SS&C

David Simpson

Connect/Reset Connect from within Native Stored Procedure
(in response to Stefan Kolev)
This is an interesting problem and I’m not sure if there’s a solution. There are no host variables in SQL PL, so if you code this:

SET V1 = ‘MYDB2’;
CONNECT TO V1;

It will actually be trying to connect to a location called V1 rather than the contents of the V1 variable.
Since the CONNECT statement cannot be run dynamically I can’t think of a way to do this. You may need to code each possible connect separately with a bunch of IF logic around them.

From: Stefan Kolev <[login to unmask email]>
Sent: Wednesday, October 9, 2019 1:23 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Connect/Reset Connect from within Native Stored Procedure


We are able to CONNECT TO location-name as long as it is hard coded. Every time I have tried using a host-variable I keep getting -950. Now I know the location name exists and I am populating the :hv with a valid value.

The manual lists these requirements for CONNECT TO:

TO location-name or host-variableIdentifies the server by the specified location name or by the location name that is contained in the host variable. If a host variable is specified:

* It must be a CHAR or VARCHAR variable with a length attribute that is not greater than 16. (A C NUL-terminated character string can be up to 17 bytes long.)
* It must not be followed by an indicator variable.
* The location name must be left-justified within the host variable and must conform to the rules for forming an ordinary identifier.
* If the length of the location name is less than the length of the host variable, it must be padded on the right with blanks.
* It must not contain lowercase characters.
* If used with an SQL procedure language application, host variable must be a qualified SQL-variable name or a qualified SQL-parameter name.

Has anyone successfully used a host variable for the location name when CONNECTing from a Native Stored Procedure?





Stefan Kolev | Database Services | |D|S|T| SS&C

-----End Original Message-----

Joe Carroll

RE: Connect/Reset Connect from within Native Stored Procedure
(in response to David Simpson)

Wish I had a more confident answer, but try qualifying your variable with either the SP name or the name of the compound statement in which is was defined.  I have some old code where I create a SP named LOCAL_SP that takes in a parameter called IN_DB2_SYS.  My connect statement looks like this:

 CONNECT TO LOCAL_SP.IN_DB2_SYS;

I'm fairly confident that this worked years ago, but it's throwing a -919 this morning.

David Simpson

Connect/Reset Connect from within Native Stored Procedure
(in response to Stefan Kolev)
Great solution!

On Oct 10, 2019 9:36 AM, Stefan Kolev <[login to unmask email]> wrote:

Thanks Joe! The answer was lurking in the last bullet point from the manual:

* If used with an SQL procedure language application, host variable must be a qualified SQL-variable name or a qualified SQL-parameter name.

All I had to do is "qualify the variable with the label from the compound statement in which it was declared."



Stefan Kolev | Database Services | |D|S|T| SS&C

-----End Original Message-----

Stefan Kolev

RE: Connect/Reset Connect from within Native Stored Procedure
(in response to Joe Carroll)

Thanks Joe!  The answer was lurking in the last bullet point from the manual:

  • If used with an SQL procedure language application, host variable must be a qualified SQL-variable name or a qualified SQL-parameter name.

All I had to do is "qualify the variable with the label from the compound statement in which it was declared."

 

Stefan Kolev | Database Services | |D|S|T| SS&C