ENVID

Roland Schiradin

ENVID
DB2V9NFM

The current SQLID controls the ENVID among others.

I try to alter a stored procedure but always get
DSNT408I SQLCODE = -4706, ERROR: ALTER PROCEDURE STATEMENT
CANNOT BE PROCESSED
BECAUSE THE OPTIONS IN EFFECT (ENVID 79) ARE NOT THE SAME AS
THE ONES
THAT WERE IN EFFECT (ENVID 86) WHEN THE PROCEDURE OR VERSION
WAS FIRST
DEFINED

select envid, substr(CURRENT_SCHEMA, 1, 8) as schema,
APPLICATION_ENCODING_CCSID as APPL_ENC_CCSID,
ORIGINAL_ENCODING_CCSID as ORIG_ENC_CCSID
from sysibm.sysenvironment
where envid = 79 or envid = 86
---------+---------+---------+---------+---------+-----
ENVID SCHEMA APPL_ENC_CCSID ORIG_ENC_CCSID
---------+---------+---------+---------+---------+-----
79 SYSP08 1141 1141
86 SYSP08 1208 1208


All others are the same.

How can I control these CCSID in DB2 Admintool Version 7.2.

Regards Roland



______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

James Campbell

Re: ENVID
(in response to Roland Schiradin)
You may need to use the same program, that created the procedure, to alter it. Look for a
package with ENCODING_CCSID=1208

The use of CCSID=1208 makes me think that perhaps it was created using a remote
connection.

James Campbell

On 24 Nov 2008 at 18:24, Roland Schiradin wrote:

> DB2V9NFM
>
> The current SQLID controls the ENVID among others.
>
> I try to alter a stored procedure but always get
> DSNT408I SQLCODE = -4706, ERROR: ALTER PROCEDURE STATEMENT
> CANNOT BE PROCESSED
> BECAUSE THE OPTIONS IN EFFECT (ENVID 79) ARE NOT THE SAME AS
> THE ONES
> THAT WERE IN EFFECT (ENVID 86) WHEN THE PROCEDURE OR VERSION
> WAS FIRST
> DEFINED
>
> select envid, substr(CURRENT_SCHEMA, 1, 8) as schema,
> APPLICATION_ENCODING_CCSID as APPL_ENC_CCSID,
> ORIGINAL_ENCODING_CCSID as ORIG_ENC_CCSID
> from sysibm.sysenvironment
> where envid = 79 or envid = 86
> ---------+---------+---------+---------+---------+-----
> ENVID SCHEMA APPL_ENC_CCSID ORIG_ENC_CCSID
> ---------+---------+---------+---------+---------+-----
> 79 SYSP08 1141 1141
> 86 SYSP08 1208 1208
>
>
> All others are the same.
>
> How can I control these CCSID in DB2 Admintool Version 7.2.
>
> Regards Roland
>
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Douwe van Sluis

Re: ENVID
(in response to James Campbell)
Roland,

What was used to create the original Stored Procedure?
Was it IBM Data Studio or something simular.
Make sure you use the same Encoding Schema or tool to do the ALTER.
I don't know if it is possible to changing the Encoding Schema in the
DB2 Admin Tool. This would require a new package bind it would think. I
do know, that the Admin Tool is not yet V9NFM ready, so maybe it is not
such a good idea to use the Admin Tool for alterations of Native SQL
SP's.

Vriendelijke groet,
Douwe van Sluis



-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Roland Schiradin
Verzonden: maandag 24 november 2008 19:24
Aan: [login to unmask email]
Onderwerp: [DB2-L] ENVID


DB2V9NFM

The current SQLID controls the ENVID among others.

I try to alter a stored procedure but always get
DSNT408I SQLCODE = -4706, ERROR: ALTER PROCEDURE STATEMENT
CANNOT BE PROCESSED
BECAUSE THE OPTIONS IN EFFECT (ENVID 79) ARE NOT THE SAME AS
THE ONES
THAT WERE IN EFFECT (ENVID 86) WHEN THE PROCEDURE OR VERSION
WAS FIRST
DEFINED


select envid, substr(CURRENT_SCHEMA, 1, 8) as schema,
APPLICATION_ENCODING_CCSID as APPL_ENC_CCSID,
ORIGINAL_ENCODING_CCSID as ORIG_ENC_CCSID
from sysibm.sysenvironment
where envid = 79 or envid = 86
---------+---------+---------+---------+---------+-----
ENVID SCHEMA APPL_ENC_CCSID ORIG_ENC_CCSID
---------+---------+---------+---------+---------+-----
79 SYSP08 1141 1141
86 SYSP08 1208 1208


All others are the same.

How can I control these CCSID in DB2 Admintool Version 7.2.

Regards Roland



______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Roland Schiradin

Re: ENVID
(in response to Douwe van Sluis)
Douwe and John,

I don't know how this SP was created. I just want to change the WLM
environment. I try AdminTool, SPUFI, DB2cmd on Windows but all fails because
of the CCSID. Db2cmd comes close but ORIG_ENC_CCSID is 1252 and not
1208.

Yes I have a complaint open because of the missing V9NFM in AdminTool.
There are several APARs open to address this but the target date is almost
next year first half or end of 2009. At the first I thought it was typo in the
APAR. I think we will see DB2 Vnext befor Admin will support DB2V9NFM.

PK60505 is the APAR among other PK7244*

Thanks
Roland

>Roland,
>
>What was used to create the original Stored Procedure?
>Was it IBM Data Studio or something simular.
>Make sure you use the same Encoding Schema or tool to do the ALTER.
>I don't know if it is possible to changing the Encoding Schema in the
>DB2 Admin Tool. This would require a new package bind it would think. I
>do know, that the Admin Tool is not yet V9NFM ready, so maybe it is not
>such a good idea to use the Admin Tool for alterations of Native SQL
>SP's.
>
>Vriendelijke groet,
>Douwe van Sluis
>
>
>
>-----Oorspronkelijk bericht-----
>Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
>Roland Schiradin
>Verzonden: maandag 24 november 2008 19:24
>Aan: [login to unmask email]
>Onderwerp: [DB2-L] ENVID
>
>
>DB2V9NFM
>
>The current SQLID controls the ENVID among others.
>
>I try to alter a stored procedure but always get
>DSNT408I SQLCODE = -4706, ERROR: ALTER PROCEDURE STATEMENT
>CANNOT BE PROCESSED
> BECAUSE THE OPTIONS IN EFFECT (ENVID 79) ARE NOT THE SAME AS
>THE ONES
> THAT WERE IN EFFECT (ENVID 86) WHEN THE PROCEDURE OR VERSION
>WAS FIRST
> DEFINED
>
>
>select envid, substr(CURRENT_SCHEMA, 1, 8) as schema,
> APPLICATION_ENCODING_CCSID as APPL_ENC_CCSID,
> ORIGINAL_ENCODING_CCSID as ORIG_ENC_CCSID
> from sysibm.sysenvironment
> where envid = 79 or envid = 86
>---------+---------+---------+---------+---------+-----
> ENVID SCHEMA APPL_ENC_CCSID ORIG_ENC_CCSID
>---------+---------+---------+---------+---------+-----
> 79 SYSP08 1141 1141
> 86 SYSP08 1208 1208
>
>
>All others are the same.
>
>How can I control these CCSID in DB2 Admintool Version 7.2.
>
>Regards Roland
>
>
>
>_________________________________________________________________
_____
>
>* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
>_________________________________________________________________
_____
>
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. The
>DB2-L list archives, FAQ, and delivery preferences are at
>http://www.idug.org/lsidug under the Listserv tab. While at the site,
>you can also access the IDUG Online Learning Center, Tech Library and
>Code Place, see the latest IDUG conference information and much more.
>If you have not yet signed up for Basic Membership in IDUG, available at
>no cost, click on Member Services at http://www.idug.org/lsms
>
>_________________________________________________________________
_____
>
>* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
>_________________________________________________________________
_____
>
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG Online
Learning Center, Tech Library and Code Place, see the latest IDUG conference
information and much more. If you have not yet signed up for Basic
Membership in IDUG, available at no cost, click on Member Services at
http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Roland Schiradin

Re: ENVID
(in response to Roland Schiradin)
During my test I discover an interesting problem.

select *
from sysibm.sysenvironment
where envid = 79 or envid = 86;
---------+---------+---------+---------+---------+---------+--
ENVID CURRENT_SCHEMA
---------+---------+---------+---------+---------+---------+--
79 EUZIESWE
86 EUZIESWE
DSNE610I NUMBER OF ROWS DISPLAYED IS 2
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS
100
---------+---------+---------+---------+---------+---------+--
select envid, substr(CURRENT_SCHEMA, 1, 8) as Cur_schema,
current_schema,
APPLICATION_ENCODING_CCSID as APPL_ENC_CCSID,
ORIGINAL_ENCODING_CCSID as ORIG_ENC_CCSID
from sysibm.sysenvironment
where envid = 79 or envid = 86
---------+---------+---------+---------+---------+---------+--
ENVID CUR_SCHEMA
---------+---------+---------+---------+---------+---------+--
79 SYSP08 SYSP08
86 SYSP08 SYSP08


Oh well CURRENT_SCHEMA is also a special register. Really a bad idea to call
the column CURRENT_SCHEMA :-(


Roland


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: ENVID
(in response to Roland Schiradin)
Hi Roland

Perhaps what you meant was

select envid, substr(E.CURRENT_SCHEMA, 1, 8) as Cur_schema,
current_schema,
APPLICATION_ENCODING_CCSID as APPL_ENC_CCSID,
ORIGINAL_ENCODING_CCSID as ORIG_ENC_CCSID
from sysibm.sysenvironment E
where envid = 79 or envid = 86

:)

Phil Grainger
CA
Senior Principal Product Manager
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company registration number 1282495 with its registered office at the address set out above. VAT number 697904179.


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Roland Schiradin
Sent: 26 November 2008 19:21
To: [login to unmask email]
Subject: Re: [DB2-L] ENVID

During my test I discover an interesting problem.

select *
from sysibm.sysenvironment
where envid = 79 or envid = 86;
---------+---------+---------+---------+---------+---------+--
ENVID CURRENT_SCHEMA
---------+---------+---------+---------+---------+---------+--
79 EUZIESWE
86 EUZIESWE
DSNE610I NUMBER OF ROWS DISPLAYED IS 2
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS
100
---------+---------+---------+---------+---------+---------+--
select envid, substr(CURRENT_SCHEMA, 1, 8) as Cur_schema,
current_schema,
APPLICATION_ENCODING_CCSID as APPL_ENC_CCSID,
ORIGINAL_ENCODING_CCSID as ORIG_ENC_CCSID
from sysibm.sysenvironment
where envid = 79 or envid = 86
---------+---------+---------+---------+---------+---------+--
ENVID CUR_SCHEMA
---------+---------+---------+---------+---------+---------+--
79 SYSP08 SYSP08
86 SYSP08 SYSP08


Oh well CURRENT_SCHEMA is also a special register. Really a bad idea to call
the column CURRENT_SCHEMA :-(


Roland


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

James Campbell

Re: ENVID
(in response to Phil Grainger)
In that case perhaps you just need to issue a
SET SCHEMA = 'EUZIESWE'
before altering the procedure.

James Campbell

On 26 Nov 2008 at 19:21, Roland Schiradin wrote:

> During my test I discover an interesting problem.
>
> select *
> from sysibm.sysenvironment
> where envid = 79 or envid = 86;
> ---------+---------+---------+---------+---------+---------+--
> ENVID CURRENT_SCHEMA
> ---------+---------+---------+---------+---------+---------+--
> 79 EUZIESWE
> 86 EUZIESWE
> DSNE610I NUMBER OF ROWS DISPLAYED IS 2
> DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS
> 100
> ---------+---------+---------+---------+---------+---------+--
> select envid, substr(CURRENT_SCHEMA, 1, 8) as Cur_schema,
> current_schema,
> APPLICATION_ENCODING_CCSID as APPL_ENC_CCSID,
> ORIGINAL_ENCODING_CCSID as ORIG_ENC_CCSID
> from sysibm.sysenvironment
> where envid = 79 or envid = 86
> ---------+---------+---------+---------+---------+---------+--
> ENVID CUR_SCHEMA
> ---------+---------+---------+---------+---------+---------+--
> 79 SYSP08 SYSP08
> 86 SYSP08 SYSP08
>
>
> Oh well CURRENT_SCHEMA is also a special register. Really a bad idea to call
> the column CURRENT_SCHEMA :-(
>
>
> Roland
>
>

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Roland Schiradin

Re: ENVID
(in response to James Campbell)
I did but the CCSID doesn't match. I have to find out how the SP was created
and recreate them. However for a native SQL procedure the WLM-Environment
is not needed. In some cases the ALTER works with RC0 but the WLM-
Enviroment remains unchanged in the DB2 catalog.

Roland


>In that case perhaps you just need to issue a
>SET SCHEMA = 'EUZIESWE'
>before altering the procedure.
>
>James Campbell
>
>On 26 Nov 2008 at 19:21, Roland Schiradin wrote:
>
>> During my test I discover an interesting problem.
>>
>> select *
>> from sysibm.sysenvironment
>> where envid = 79 or envid = 86;
>> ---------+---------+---------+---------+---------+---------+--
>> ENVID CURRENT_SCHEMA
>> ---------+---------+---------+---------+---------+---------+--
>> 79 EUZIESWE
>> 86 EUZIESWE
>> DSNE610I NUMBER OF ROWS DISPLAYED IS 2
>> DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS
>> 100
>> ---------+---------+---------+---------+---------+---------+--
>> select envid, substr(CURRENT_SCHEMA, 1, 8) as Cur_schema,
>> current_schema,
>> APPLICATION_ENCODING_CCSID as APPL_ENC_CCSID,
>> ORIGINAL_ENCODING_CCSID as ORIG_ENC_CCSID
>> from sysibm.sysenvironment
>> where envid = 79 or envid = 86
>> ---------+---------+---------+---------+---------+---------+--
>> ENVID CUR_SCHEMA
>> ---------+---------+---------+---------+---------+---------+--
>> 79 SYSP08 SYSP08
>> 86 SYSP08 SYSP08
>>
>>
>> Oh well CURRENT_SCHEMA is also a special register. Really a bad idea to
call
>> the column CURRENT_SCHEMA :-(
>>
>>
>> Roland
>>
>>
>
>_________________________________________________________________
_____
>
>* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
>_________________________________________________________________
_____
>
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services at
http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Roland Schiradin

Re: ENVID
(in response to Roland Schiradin)
Hi Phil,
yes this works also like

SELECT ENVID, SUBSTR("CURRENT_SCHEMA", 1, 8) AS CUR_SCHEMA,
CURRENT_SCHEMA,
APPLICATION_ENCODING_CCSID AS APPL_ENC_CCSID,
ORIGINAL_ENCODING_CCSID AS ORIG_ENC_CCSID
FROM SYSIBM.SYSENVIRONMENT
where envid = 79 or envid = 86

Roland

>Hi Roland
>
>Perhaps what you meant was
>
>select envid, substr(E.CURRENT_SCHEMA, 1, 8) as Cur_schema,
> current_schema,
> APPLICATION_ENCODING_CCSID as APPL_ENC_CCSID,
> ORIGINAL_ENCODING_CCSID as ORIG_ENC_CCSID
> from sysibm.sysenvironment E
> where envid = 79 or envid = 86
>
>:)
>
>Phil Grainger
>CA
>Senior Principal Product Manager
>Phone: +44 (0)1753 577 733
>Mobile: +44 (0)7970 125 752
>eMail: [login to unmask email]
>
>Ditton Park
>Riding Court Road
>Datchet
>Slough
>SL3 9LL
>
>CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address set out
above. VAT number 697904179.
>
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Roland Schiradin
>Sent: 26 November 2008 19:21
>To: [login to unmask email]
>Subject: Re: [DB2-L] ENVID
>
>During my test I discover an interesting problem.
>
> select *
> from sysibm.sysenvironment
> where envid = 79 or envid = 86;
>---------+---------+---------+---------+---------+---------+--
> ENVID CURRENT_SCHEMA
>---------+---------+---------+---------+---------+---------+--
> 79 EUZIESWE
> 86 EUZIESWE
>DSNE610I NUMBER OF ROWS DISPLAYED IS 2
>DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS
>100
>---------+---------+---------+---------+---------+---------+--
>select envid, substr(CURRENT_SCHEMA, 1, 8) as Cur_schema,
> current_schema,
> APPLICATION_ENCODING_CCSID as APPL_ENC_CCSID,
> ORIGINAL_ENCODING_CCSID as ORIG_ENC_CCSID
> from sysibm.sysenvironment
> where envid = 79 or envid = 86
>---------+---------+---------+---------+---------+---------+--
> ENVID CUR_SCHEMA
>---------+---------+---------+---------+---------+---------+--
> 79 SYSP08 SYSP08
> 86 SYSP08 SYSP08
>
>
>Oh well CURRENT_SCHEMA is also a special register. Really a bad idea to call
>the column CURRENT_SCHEMA :-(
>
>
>Roland
>
>
>_________________________________________________________________
_____
>
>* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
>_________________________________________________________________
_____
>
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services at
http://www.idug.org/lsms
>
>_________________________________________________________________
_____
>
>* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
>_________________________________________________________________
_____
>
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services at
http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: ENVID
(in response to Roland Schiradin)
Hmm

I'm surprised just enclosing the column name in quotes is enough to remove the parsers confusion, but I guess it makes sense

I still prefer the correlation name approach as it makes it clear to people (as well as parsers) that we mean a column name and not something else

Phil Grainger
CA
Senior Principal Product Manager
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company registration number 1282495 with its registered office at the address set out above. VAT number 697904179.



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Roland Schiradin
Sent: 27 November 2008 15:39
To: [login to unmask email]
Subject: Re: [DB2-L] ENVID

Hi Phil,
yes this works also like

SELECT ENVID, SUBSTR("CURRENT_SCHEMA", 1, 8) AS CUR_SCHEMA,
CURRENT_SCHEMA,
APPLICATION_ENCODING_CCSID AS APPL_ENC_CCSID,
ORIGINAL_ENCODING_CCSID AS ORIG_ENC_CCSID
FROM SYSIBM.SYSENVIRONMENT
where envid = 79 or envid = 86

Roland

>Hi Roland
>
>Perhaps what you meant was
>
>select envid, substr(E.CURRENT_SCHEMA, 1, 8) as Cur_schema,
> current_schema,
> APPLICATION_ENCODING_CCSID as APPL_ENC_CCSID,
> ORIGINAL_ENCODING_CCSID as ORIG_ENC_CCSID
> from sysibm.sysenvironment E
> where envid = 79 or envid = 86
>
>:)
>
>Phil Grainger
>CA
>Senior Principal Product Manager
>Phone: +44 (0)1753 577 733
>Mobile: +44 (0)7970 125 752
>eMail: [login to unmask email]
>
>Ditton Park
>Riding Court Road
>Datchet
>Slough
>SL3 9LL
>
>CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address set out
above. VAT number 697904179.
>
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Roland Schiradin
>Sent: 26 November 2008 19:21
>To: [login to unmask email]
>Subject: Re: [DB2-L] ENVID
>
>During my test I discover an interesting problem.
>
> select *
> from sysibm.sysenvironment
> where envid = 79 or envid = 86;
>---------+---------+---------+---------+---------+---------+--
> ENVID CURRENT_SCHEMA
>---------+---------+---------+---------+---------+---------+--
> 79 EUZIESWE
> 86 EUZIESWE
>DSNE610I NUMBER OF ROWS DISPLAYED IS 2
>DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS
>100
>---------+---------+---------+---------+---------+---------+--
>select envid, substr(CURRENT_SCHEMA, 1, 8) as Cur_schema,
> current_schema,
> APPLICATION_ENCODING_CCSID as APPL_ENC_CCSID,
> ORIGINAL_ENCODING_CCSID as ORIG_ENC_CCSID
> from sysibm.sysenvironment
> where envid = 79 or envid = 86
>---------+---------+---------+---------+---------+---------+--
> ENVID CUR_SCHEMA
>---------+---------+---------+---------+---------+---------+--
> 79 SYSP08 SYSP08
> 86 SYSP08 SYSP08
>
>
>Oh well CURRENT_SCHEMA is also a special register. Really a bad idea to call
>the column CURRENT_SCHEMA :-(
>
>
>Roland
>
>
>_________________________________________________________________
_____
>
>* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
>_________________________________________________________________
_____
>
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services at
http://www.idug.org/lsms
>
>_________________________________________________________________
_____
>
>* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
>_________________________________________________________________
_____
>
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services at
http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Roland Schiradin

Re: ENVID
(in response to Phil Grainger)
I agree the correlation is a better solution even I still believe it was a bad idea
to assign such a column name for a catalog table.

Roland

>Hmm
>
>I'm surprised just enclosing the column name in quotes is enough to remove
the parsers confusion, but I guess it makes sense
>
>I still prefer the correlation name approach as it makes it clear to people (as
well as parsers) that we mean a column name and not something else
>

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

James Campbell

Re: ENVID
(in response to Roland Schiradin)
This is an off-the-wall possibility. Might work ...

Use DSNTIAUL (not DSNTEP2, DSNTIAD, etc). Issue
SET SCHEMA = 'EUZIESWE';
SET CURRENT ENCODING SCHEME = '1208' ;
(to prepare for what comes next, put the last semi-colon in col 72)
Everything, including spaces, that follows is in Unicode UFT-8. Effectively,
provided you keep to characters less than x'7F', you can use ASCII:
ALTER PROCEDURE .... ; Or, to put it another way:
x'414c5445522050 .... 3b202020...'

Actually, provided there is only one statement in Unicode, it might work in
DSNTEP2 etc. Depends on how they handle the SQLCODE = 98.

James Campbell

On 27 Nov 2008 at 14:36, Roland Schiradin wrote:

> I did but the CCSID doesn't match. I have to find out how the SP was created
> and recreate them. However for a native SQL procedure the WLM-Environment
> is not needed. In some cases the ALTER works with RC0 but the WLM-
> Enviroment remains unchanged in the DB2 catalog.
>
> Roland
>
>
> >In that case perhaps you just need to issue a
> >SET SCHEMA = 'EUZIESWE'
> >before altering the procedure.
> >
> >James Campbell
> >
> >On 26 Nov 2008 at 19:21, Roland Schiradin wrote:
> >
> >> During my test I discover an interesting problem.
> >>
> >> select *
> >> from sysibm.sysenvironment
> >> where envid = 79 or envid = 86;
> >> ---------+---------+---------+---------+---------+---------+--
> >> ENVID CURRENT_SCHEMA
> >> ---------+---------+---------+---------+---------+---------+--
> >> 79 EUZIESWE
> >> 86 EUZIESWE
> >> DSNE610I NUMBER OF ROWS DISPLAYED IS 2
> >> DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS
> >> 100
> >> ---------+---------+---------+---------+---------+---------+--
> >> select envid, substr(CURRENT_SCHEMA, 1, 8) as Cur_schema,
> >> current_schema,
> >> APPLICATION_ENCODING_CCSID as APPL_ENC_CCSID,
> >> ORIGINAL_ENCODING_CCSID as ORIG_ENC_CCSID
> >> from sysibm.sysenvironment
> >> where envid = 79 or envid = 86
> >> ---------+---------+---------+---------+---------+---------+--
> >> ENVID CUR_SCHEMA
> >> ---------+---------+---------+---------+---------+---------+--
> >> 79 SYSP08 SYSP08
> >> 86 SYSP08 SYSP08
> >>
> >>
> >> Oh well CURRENT_SCHEMA is also a special register. Really a bad idea to
> call
> >> the column CURRENT_SCHEMA :-(
> >>
> >>
> >> Roland
> >>
> >>
> >
> >_________________________________________________________________
> _____
> >
> >* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
> >_________________________________________________________________
> _____
> >
> >
> >
> >The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug
> under the Listserv tab. While at the site, you can also access the IDUG
> Online Learning Center, Tech Library and Code Place, see the latest IDUG
> conference information and much more. If you have not yet signed up for
> Basic Membership in IDUG, available at no cost, click on Member Services at
> http://www.idug.org/lsms
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms