Data Studio and DB2 11 for z/OS NFM features?

David Churn

Data Studio and DB2 11 for z/OS NFM features?

Listers,

I am working with Data Studio 4.1.0 and 4.1.2, and a stored procedure on DB2 11 for z/OS, NFM with APPLCOMPAT V11R1.  Data Studio is marking the new features of DB2 11 as errors.  It does this with the AUTONOMOUS keyword and trying to declare an ARRAY field.  How do I tell Data Studio to use the DB2 11 features?

I have been through the many property options and do not see anything mentioning version.  Same for the project properties.  Data Studio has been helpfully telling me the the system is "DB2 for z/OS V11 (NEW-Function Mode)".  I ran the zparm stored procedure, SYSPROC.ADMIN_INFO_SYSPARM, and it says APPLCOMPAT is "V11R1".

I have created an autonomous stored procedure using SPUFI.  When I try to edit it in Data Studio, it tells me, '"EXTERNAL" was expected instead of "AUTONOMOUS".'  It will not let me re-install the autonomous stored procedure.

My only conclusion is that this is an issue with Data Studio.

Here is a copy of the CREATE

CREATE PROCEDURE ARZSTPR.SPLOGIT                                         
    ( IN  PROGRAM_NM          VARCHAR(128)                                
    , IN  OPERATOR_ID         VARCHAR(128)                              
    , IN  PROGRAM_LOCATION_TX VARCHAR(7)                                  
    , IN  FILE_NM             VARCHAR(30)                              
    , IN  IN_SQLSTATE         CHAR(5)                                   
    , IN  IN_SQLCODE          INTEGER                                  
    , IN  MESSAGE_ID          VARCHAR(8)                                 
    , IN  WORKSTATION_ID      VARCHAR(20)                                 
    , IN  ERROR_TX            VARCHAR(3000) )
  VERSION V1                                                         
  QUALIFIER ARZ                                                     
  SQL PATH  SYSIBM,SYSFUN,SYSPROC,ARZSTPR                            
  PACKAGE OWNER MFDBCON                                                  
  LANGUAGE SQL                                                        
  ISOLATION LEVEL CS                                                      
  VALIDATE RUN                                                       
  ALLOW DEBUG MODE                                                      
  ASUTIME LIMIT 300000                                               
  AUTONOMOUS                                                        <--  red "x" here
  APPLCOMPAT V11R1   

 

David Churn

Myron Miller

Data Studio and DB2 11 for z/OS NFM features?
(in response to David Churn)
I think it's a data studio bug. i've found the UNNEST command for ARRAYs in SPs in not supported when one does query tuning. It's turning out that many of the V11 Sp features are not supported in Data Studio for some unknown reason. Like it hasn't been updated for V11 or V12.


Thanks Myron W. Miller


________________________________
From: David Churn <[login to unmask email]>
Sent: Tuesday, October 3, 2017 12:48 PM
To: [login to unmask email]
Subject: [DB2-L] - Data Studio and DB2 11 for z/OS NFM features?


Listers,

I am working with Data Studio 4.1.0 and 4.1.2, and a stored procedure on DB2 11 for z/OS, NFM with APPLCOMPAT V11R1. Data Studio is marking the new features of DB2 11 as errors. It does this with the AUTONOMOUS keyword and trying to declare an ARRAY field. How do I tell Data Studio to use the DB2 11 features?

I have been through the many property options and do not see anything mentioning version. Same for the project properties. Data Studio has been helpfully telling me the the system is "DB2 for z/OS V11 (NEW-Function Mode)". I ran the zparm stored procedure, SYSPROC.ADMIN_INFO_SYSPARM, and it says APPLCOMPAT is "V11R1".

I have created an autonomous stored procedure using SPUFI. When I try to edit it in Data Studio, it tells me, '"EXTERNAL" was expected instead of "AUTONOMOUS".' It will not let me re-install the autonomous stored procedure.

My only conclusion is that this is an issue with Data Studio.

Here is a copy of the CREATE

CREATE PROCEDURE ARZSTPR.SPLOGIT
( IN PROGRAM_NM VARCHAR(128)
, IN OPERATOR_ID VARCHAR(128)
, IN PROGRAM_LOCATION_TX VARCHAR(7)
, IN FILE_NM VARCHAR(30)
, IN IN_SQLSTATE CHAR(5)
, IN IN_SQLCODE INTEGER
, IN MESSAGE_ID VARCHAR(8)
, IN WORKSTATION_ID VARCHAR(20)
, IN ERROR_TX VARCHAR(3000) )
VERSION V1
QUALIFIER ARZ
SQL PATH SYSIBM,SYSFUN,SYSPROC,ARZSTPR
PACKAGE OWNER MFDBCON
LANGUAGE SQL
ISOLATION LEVEL CS
VALIDATE RUN
ALLOW DEBUG MODE
ASUTIME LIMIT 300000
AUTONOMOUS <-- red "x" here
APPLCOMPAT V11R1



David Churn

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

Daniel Luksetich

Data Studio and DB2 11 for z/OS NFM features?
(in response to Myron Miller)


have you downloaded and installed all the fixpacks for Data Studio? I
know development is down to a crawl for Data Studio, but I know they
have added some features especially in support of Native REST API, and
that may include stored procedures.

Dan

On 10.03.2017 09:54, Myron Miller wrote:

> I think it's a data studio bug. i've found the UNNEST command for ARRAYs in SPs in not supported when one does query tuning. It's turning out that many of the V11 Sp features are not supported in Data Studio for some unknown reason. Like it hasn't been updated for V11 or V12.
>
> Thanks Myron W. Miller
>
> -------------------------
>
> FROM: David Churn <[login to unmask email]>
> SENT: Tuesday, October 3, 2017 12:48 PM
> TO: [login to unmask email]
> SUBJECT: [DB2-L] - Data Studio and DB2 11 for z/OS NFM features?
>
> Listers,
>
> I am working with Data Studio 4.1.0 and 4.1.2, and a stored procedure on DB2 11 for z/OS, NFM with APPLCOMPAT V11R1. Data Studio is marking the new features of DB2 11 as errors. It does this with the AUTONOMOUS keyword and trying to declare an ARRAY field. How do I tell Data Studio to use the DB2 11 features?
>
> I have been through the many property options and do not see anything mentioning version. Same for the project properties. Data Studio has been helpfully telling me the the system is "DB2 for z/OS V11 (NEW-Function Mode)". I ran the zparm stored procedure, SYSPROC.ADMIN_INFO_SYSPARM, and it says APPLCOMPAT is "V11R1".
>
> I have created an autonomous stored procedure using SPUFI. When I try to edit it in Data Studio, it tells me, '"EXTERNAL" was expected instead of "AUTONOMOUS".' It will not let me re-install the autonomous stored procedure.
>
> My only conclusion is that this is an issue with Data Studio.
>
> Here is a copy of the CREATE
>
> CREATE PROCEDURE ARZSTPR.SPLOGIT
> ( IN PROGRAM_NM VARCHAR(128)
> , IN OPERATOR_ID VARCHAR(128)
> , IN PROGRAM_LOCATION_TX VARCHAR(7)
> , IN FILE_NM VARCHAR(30)
> , IN IN_SQLSTATE CHAR(5)
> , IN IN_SQLCODE INTEGER
> , IN MESSAGE_ID VARCHAR(8)
> , IN WORKSTATION_ID VARCHAR(20)
> , IN ERROR_TX VARCHAR(3000) )
> VERSION V1
> QUALIFIER ARZ
> SQL PATH SYSIBM,SYSFUN,SYSPROC,ARZSTPR
> PACKAGE OWNER MFDBCON
> LANGUAGE SQL
> ISOLATION LEVEL CS
> VALIDATE RUN
> ALLOW DEBUG MODE
> ASUTIME LIMIT 300000
> AUTONOMOUS <-- red "x" here
> APPLCOMPAT V11R1
>
> David Churn
>
> -----End Original Message-----
>
> -----End Original Message-----


Links:
------
[1] http://www.idug.org/p/fo/st/?post=183164&amp;anc=p183164#p183164
[2] http://www.idug.org/p/fo/si/?topic=19
[3] http://www.idug.org/p/us/to/
[4] http://www.ESAIGroup.com/idug
[5] http://www.idug.org/p/cm/ld/fid=2

Michael Arlebrandt

Data Studio and DB2 11 for z/OS NFM features?
(in response to Daniel Luksetich)
Have you checked the applcompat level for the jdbc packages? Maybe they still are bound with an old level. I know it has to be done for db2 connect packages but data studio runs Java so I believe there are other packages for that

We just had a lesson about that today here in Lisbon :)

Best regards
Michael
Den 3 okt. 2017, kI 18:03, Daniel Luksetich <[login to unmask email]<mailto:[login to unmask email]>> skrev:

have you downloaded and installed all the fixpacks for Data Studio? I know development is down to a crawl for Data Studio, but I know they have added some features especially in support of Native REST API, and that may include stored procedures.

Dan





On 10.03.2017 09:54, Myron Miller wrote:

I think it's a data studio bug. i've found the UNNEST command for ARRAYs in SPs in not supported when one does query tuning. It's turning out that many of the V11 Sp features are not supported in Data Studio for some unknown reason. Like it hasn't been updated for V11 or V12.



Thanks Myron W. Miller


________________________________
From: David Churn <[login to unmask email]>
Sent: Tuesday, October 3, 2017 12:48 PM
To: [login to unmask email]
Subject: [DB2-L] - Data Studio and DB2 11 for z/OS NFM features?


Listers,

I am working with Data Studio 4.1.0 and 4.1.2, and a stored procedure on DB2 11 for z/OS, NFM with APPLCOMPAT V11R1. Data Studio is marking the new features of DB2 11 as errors. It does this with the AUTONOMOUS keyword and trying to declare an ARRAY field. How do I tell Data Studio to use the DB2 11 features?

I have been through the many property options and do not see anything mentioning version. Same for the project properties. Data Studio has been helpfully telling me the the system is "DB2 for z/OS V11 (NEW-Function Mode)". I ran the zparm stored procedure, SYSPROC.ADMIN_INFO_SYSPARM, and it says APPLCOMPAT is "V11R1".

I have created an autonomous stored procedure using SPUFI. When I try to edit it in Data Studio, it tells me, '"EXTERNAL" was expected instead of "AUTONOMOUS".' It will not let me re-install the autonomous stored procedure.

My only conclusion is that this is an issue with Data Studio.

Here is a copy of the CREATE

CREATE PROCEDURE ARZSTPR.SPLOGIT
( IN PROGRAM_NM VARCHAR(128)
, IN OPERATOR_ID VARCHAR(128)
, IN PROGRAM_LOCATION_TX VARCHAR(7)
, IN FILE_NM VARCHAR(30)
, IN IN_SQLSTATE CHAR(5)
, IN IN_SQLCODE INTEGER
, IN MESSAGE_ID VARCHAR(8)
, IN WORKSTATION_ID VARCHAR(20)
, IN ERROR_TX VARCHAR(3000) )
VERSION V1
QUALIFIER ARZ
SQL PATH SYSIBM,SYSFUN,SYSPROC,ARZSTPR
PACKAGE OWNER MFDBCON
LANGUAGE SQL
ISOLATION LEVEL CS
VALIDATE RUN
ALLOW DEBUG MODE
ASUTIME LIMIT 300000
AUTONOMOUS <-- red "x" here
APPLCOMPAT V11R1



David Churn

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

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

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


::DISCLAIMER::
----------------------------------------------------------------------------------------------------------------------------------------------------

The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only.
E-mail transmission is not guaranteed to be secure or error-free as information could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or may contain viruses in transmission. The e mail and its contents
(with or without referred errors) shall therefore not attach any liability on the originator or HCL or its affiliates.
Views or opinions, if any, presented in this email are solely those of the author and may not necessarily reflect the
views or opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of this message without the prior written consent of authorized representative of
HCL is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately.
Before opening any email and/or attachments, please check them for viruses and other defects.

----------------------------------------------------------------------------------------------------------------------------------------------------

David Churn

Data Studio and DB2 11 for z/OS NFM features?
(in response to Michael Arlebrandt)
Thanks to all for the suggestions. I am following up with our systems folks. I’ll post a resolution when it’s working.

David Churn

> On Oct 3, 2017, at 12:15 PM, Michael Arlebrandt <[login to unmask email]> wrote:
>
> Have you checked the applcompat level for the jdbc packages? Maybe they still are bound with an old level. I know it has to be done for db2 connect packages but data studio runs Java so I believe there are other packages for that
>
> We just had a lesson about that today here in Lisbon :)
>
> Best regards
> Michael
> Den 3 okt. 2017, kI 18:03, Daniel Luksetich <[login to unmask email]> skrev:
>>
>> have you downloaded and installed all the fixpacks for Data Studio? I know development is down to a crawl for Data Studio, but I know they have added some features especially in support of Native REST API, and that may include stored procedures.
>>
>> Dan
>>
>>
>>
>>
>>> On 10.03.2017 09:54, Myron Miller wrote:
>>>
>>> I think it's a data studio bug. i've found the UNNEST command for ARRAYs in SPs in not supported when one does query tuning. It's turning out that many of the V11 Sp features are not supported in Data Studio for some unknown reason. Like it hasn't been updated for V11 or V12.
>>>
>>>
>>>
>>> Thanks Myron W. Miller
>>>
>>>
>>> From: David Churn <[login to unmask email]>
>>> Sent: Tuesday, October 3, 2017 12:48 PM
>>> To: [login to unmask email]
>>> Subject: [DB2-L] - Data Studio and DB2 11 for z/OS NFM features?
>>>
>>> Listers,
>>>
>>> I am working with Data Studio 4.1.0 and 4.1.2, and a stored procedure on DB2 11 for z/OS, NFM with APPLCOMPAT V11R1. Data Studio is marking the new features of DB2 11 as errors. It does this with the AUTONOMOUS keyword and trying to declare an ARRAY field. How do I tell Data Studio to use the DB2 11 features?
>>>
>>> I have been through the many property options and do not see anything mentioning version. Same for the project properties. Data Studio has been helpfully telling me the the system is "DB2 for z/OS V11 (NEW-Function Mode)". I ran the zparm stored procedure, SYSPROC.ADMIN_INFO_SYSPARM, and it says APPLCOMPAT is "V11R1".
>>>
>>> I have created an autonomous stored procedure using SPUFI. When I try to edit it in Data Studio, it tells me, '"EXTERNAL" was expected instead of "AUTONOMOUS".' It will not let me re-install the autonomous stored procedure.
>>>
>>> My only conclusion is that this is an issue with Data Studio.
>>>
>>> Here is a copy of the CREATE
>>>
>>> CREATE PROCEDURE ARZSTPR.SPLOGIT
>>> ( IN PROGRAM_NM VARCHAR(128)
>>> , IN OPERATOR_ID VARCHAR(128)
>>> , IN PROGRAM_LOCATION_TX VARCHAR(7)
>>> , IN FILE_NM VARCHAR(30)
>>> , IN IN_SQLSTATE CHAR(5)
>>> , IN IN_SQLCODE INTEGER
>>> , IN MESSAGE_ID VARCHAR(8)
>>> , IN WORKSTATION_ID VARCHAR(20)
>>> , IN ERROR_TX VARCHAR(3000) )
>>> VERSION V1
>>> QUALIFIER ARZ
>>> SQL PATH SYSIBM,SYSFUN,SYSPROC,ARZSTPR
>>> PACKAGE OWNER MFDBCON
>>> LANGUAGE SQL
>>> ISOLATION LEVEL CS
>>> VALIDATE RUN
>>> ALLOW DEBUG MODE
>>> ASUTIME LIMIT 300000
>>> AUTONOMOUS <-- red "x" here
>>> APPLCOMPAT V11R1
>>>
>>>
>>>
>>> David Churn
>>>
>>>
>>> -----End Original Message-----
>>>
>>> -----End Original Message-----
>>
>> -----End Original Message-----
>
>
>
> ::DISCLAIMER::
> ----------------------------------------------------------------------------------------------------------------------------------------------------
> The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only.
> E-mail transmission is not guaranteed to be secure or error-free as information could be intercepted, corrupted,
> lost, destroyed, arrive late or incomplete, or may contain viruses in transmission. The e mail and its contents
> (with or without referred errors) shall therefore not attach any liability on the originator or HCL or its affiliates.
> Views or opinions, if any, presented in this email are solely those of the author and may not necessarily reflect the
> views or opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification,
> distribution and / or publication of this message without the prior written consent of authorized representative of
> HCL is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately.
> Before opening any email and/or attachments, please check them for viruses and other defects.
> ----------------------------------------------------------------------------------------------------------------------------------------------------
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
> ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
> http://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>