Re: need help

Michael Hannan

Re: need help
(in response to Mohammed Nayeem)
Sanjeev,

Any subquery of form:

column = (subquery)

requires that the subquery return a single column and single row.
If multiple rows returned, the SQL terminates with an error code.

Exactly when the subquery is executed depends on whether correlated or not.

The SET clause would have to be executed in stage 2 processing after the
subquery is executed for each outer row found (if it is correlated to outer
query).

It appears from the SQL that table tt1_cust_conv_stus is related to ttl_bckt
(probably the parent) via join column bckt_id. Similarly ttl_bckt is
related to tt1_sub_prcs (probably the parent) via join column sub_prcs_id.
This is evident in the SQL.

>From: [login to unmask email]
>Subject: Re: need help
>To: [login to unmask email]
>
>Hi all,
>
>If it is possible with DB2 V6 for OS390 APAR PQ30383 then doesn't it mean
>that DB2 has to determine the relation among the tables(dynamically) and
>then find out if only one record is qualifying for the result(used in the
>SET clause) then only use it in the SET clause.
>I am really wondering how is this possible.
>Can somebody please explain me the way this query works and what is the
>kind of relation three tables used should have.
>
>Thanks for any help
>
>Sanjeev
>
>
>
>
>Sunil M Gupte <[login to unmask email]>@RYCI.COM> on 12/17/99
>11:54:28 PM
>
>Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
>Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>
>
>To: [login to unmask email]
>cc:
>Subject: need help
>
>
>Hi
>
>Can somebody please point out if following is allowed in DB2 ? (OS/390)
>Specifically the Select in the Set statement ?
>
>Update test1.tt1_cust_conv_stus
>Set last_upd_user_id = userid,
> bckt_id = (Select bckt_id
> From test1.tt1_sub_prcs a,
> Test1.tt1_bckt b
> Where sub_prcs_nme = SUBPROCESS2
> and b.bckt_nme = BUCKET2
> and a.sub_prcs_id = b.sub_prcs_id)
> where cms_id = CMSID
>
>Thank u.
>
>Sunil Gupte



Marg Gozdowski

need help with drda
Well, I am still trying to get the pc to mainframe working.
 
We are using Microsoft's ODBC Driver - tcp/ip setup - ar only - & on the
mainframe, we are getting err msg:
 
00D31050
 
Explanation:  DB2 expected to receive security information for the remote
client but a valid DRDA SECCHK command was not received for a connection
request from a DRDA application requester (AR). The DRDA SECCHK command is
only valid with a DRDA security manager level greater than or equal to 5.
 
This can be the case if one of the following occurs:
 
   The DRDA AR requested a DRDA security manager less than level 5 for an
SNA connection where the APPL command for DB2 was specified with the
SECACPT=NONE option.
 
   The DRDA SECCHK command did not contain the required security
information.
 
   The DRDA AR requested a DRDA security manager less than level 5 for a
TCP/IP connection.
 
 
This abend reason code is issued by the following CSECTs: DSNLTEXC,
DSNLTSEC.
 
System Action:  The attempt to access the local database resource fails, the
failure is reported to the SQL application, and the conversation is
terminated.
 
System Programmer Response:  For SNA connections, if SECACPT=NONE and the
DRDA AR does not support DRDA security manager level 5, contact the DB2
security administrator to change the SECACPT option to ALREADYV or CONV to
allow for security information in the VTAM FMH-5 in the form of userid or
userid/password fields. If SECACPT=NONE is not changed, then DB2 cannot
accept connection requests from this remote client.
 
For SNA connections, If SECACPT=ALREADYV or CONV, then even though userid or
userid/password fields in the FMH-5 are received and validated, DB2 must
evaluate a DRDA SECCHK command if it is sent. If the DRDA AR requests this
level of security but does not provide the correct information in the form
of a SECCHK command, this is a DRDA protocol error committed by the DRDA AR
implementation at the remote client.
 
For TCP/IP connections, this is a DRDA protocol error committed by the DRDA
AR implementation at the remote client.
 
 
I also have a trace that says it has had an MSACCESS sql err - sqlcode=-576
(no where to be found on mainframe db2 doc)
 trace says sql err on "SELECT Config, nValue FROM MSysConf\ 0"
 
Has anyone else had this problem? If so, how do you fix it?
 
If not, can you point me in some direction to find doc on what I should be
looking for or a msg manual?
 
We have a contract person working on the pc side & he is unable to find the
doc we need.
 
He is trying to set this up for dynamic sql (if that makes any difference).
 
TIA, Marg
 



Piontkowski Michael ML

Re: need help with drda
(in response to Marg Gozdowski)
I'm assuming your connecting to a DB2 for OS/390 V5.1
subsystem. I'm not familiar with Microsoft's ODBC driver.
Does it support the DRDA protocol across TCP/IP?
Does it request and pass on to DB2 a userid & password?

We're using the StarSQL ODBC driver on Win95 across
TCP/IP into DB2 for OS/390 V5.1 on OS/390 V2.5. The
ODBC driver requests & passes on to DB2 a userid &
password as part of the connection process. DB2 calls
RACF to verify that the userid & password is valid.

See the section called "Controlling Inbound TCP/IP
Connections that use DRDA Security Mechanisms"
in the DB2 for OS/390 V5 Administration Guide
Volume 1 (SC26-8957 Chapter 3) for an explanation
of the authorization process for a TCP/IP connection.



Mike Piontkowski
Voice/Fax: 302.886.4612
mailto:[login to unmask email]

> ----------
> From: Marg Gozdowski[SMTP:[login to unmask email]
> Sent: Tuesday, November 30, 1999 4:29 PM
> To: [login to unmask email]
> Subject: [DB2-L] need help with drda
>
> Well, I am still trying to get the pc to mainframe working.
>
> We are using Microsoft's ODBC Driver - tcp/ip setup - ar only - & on the
> mainframe, we are getting err msg:
>
> 00D31050
>
> Explanation: DB2 expected to receive security information for the remote
> client but a valid DRDA SECCHK command was not received for a connection
> request from a DRDA application requester (AR). The DRDA SECCHK command is
> only valid with a DRDA security manager level greater than or equal to 5.
>
> This can be the case if one of the following occurs:
>
> The DRDA AR requested a DRDA security manager less than level 5 for an
> SNA connection where the APPL command for DB2 was specified with the
> SECACPT=NONE option.
>
> The DRDA SECCHK command did not contain the required security
> information.
>
> The DRDA AR requested a DRDA security manager less than level 5 for a
> TCP/IP connection.
>
>
> This abend reason code is issued by the following CSECTs: DSNLTEXC,
> DSNLTSEC.
>
> System Action: The attempt to access the local database resource fails,
> the
> failure is reported to the SQL application, and the conversation is
> terminated.
>
> System Programmer Response: For SNA connections, if SECACPT=NONE and the
> DRDA AR does not support DRDA security manager level 5, contact the DB2
> security administrator to change the SECACPT option to ALREADYV or CONV to
> allow for security information in the VTAM FMH-5 in the form of userid or
> userid/password fields. If SECACPT=NONE is not changed, then DB2 cannot
> accept connection requests from this remote client.
>
> For SNA connections, If SECACPT=ALREADYV or CONV, then even though userid
> or
> userid/password fields in the FMH-5 are received and validated, DB2 must
> evaluate a DRDA SECCHK command if it is sent. If the DRDA AR requests this
> level of security but does not provide the correct information in the form
> of a SECCHK command, this is a DRDA protocol error committed by the DRDA
> AR
> implementation at the remote client.
>
> For TCP/IP connections, this is a DRDA protocol error committed by the
> DRDA
> AR implementation at the remote client.
>
>
> I also have a trace that says it has had an MSACCESS sql err -
> sqlcode=-576
> (no where to be found on mainframe db2 doc)
> trace says sql err on "SELECT Config, nValue FROM MSysConf\ 0"
>
> Has anyone else had this problem? If so, how do you fix it?
>
> If not, can you point me in some direction to find doc on what I should be
> looking for or a msg manual?
>
> We have a contract person working on the pc side & he is unable to find
> the
> doc we need.
>
> He is trying to set this up for dynamic sql (if that makes any
> difference).
>
> TIA, Marg
>
>
>
>
> http://www.ryci.com/db2-l. The owners of the list can be reached at
> [login to unmask email]
>



Philip Gunning

Re: need help with drda
(in response to Piontkowski Michael ML)
Marg, Sounds like you need to change the VTAM appl SECACPT DDF option to
ALREADYV. Have your VTAM person check this out. HTH Phil

Philip K. Gunning
DB2 DBA
IBM Certified Solutions Expert -- DB2 UDB
IBM Cetfified Solutions Expert -- CICS TS
Assoc List Owner

-----Original Message-----
From: Marg Gozdowski [SMTP:[login to unmask email]
Sent: Tuesday, November 30, 1999 1:29 PM
To: [login to unmask email]
Subject: need help with drda

Well, I am still trying to get the pc to mainframe working.

We are using Microsoft's ODBC Driver - tcp/ip setup - ar only - & on the
mainframe, we are getting err msg:

00D31050

Explanation: DB2 expected to receive security information for the remote
client but a valid DRDA SECCHK command was not received for a connection
request from a DRDA application requester (AR). The DRDA SECCHK command is
only valid with a DRDA security manager level greater than or equal to 5.

This can be the case if one of the following occurs:

The DRDA AR requested a DRDA security manager less than level 5 for an
SNA connection where the APPL command for DB2 was specified with the
SECACPT=NONE option.

The DRDA SECCHK command did not contain the required security
information.

The DRDA AR requested a DRDA security manager less than level 5 for a
TCP/IP connection.


This abend reason code is issued by the following CSECTs: DSNLTEXC,
DSNLTSEC.

System Action: The attempt to access the local database resource fails,
the
failure is reported to the SQL application, and the conversation is
terminated.

System Programmer Response: For SNA connections, if SECACPT=NONE and the
DRDA AR does not support DRDA security manager level 5, contact the DB2
security administrator to change the SECACPT option to ALREADYV or CONV to
allow for security information in the VTAM FMH-5 in the form of userid or
userid/password fields. If SECACPT=NONE is not changed, then DB2 cannot
accept connection requests from this remote client.

For SNA connections, If SECACPT=ALREADYV or CONV, then even though userid
or
userid/password fields in the FMH-5 are received and validated, DB2 must
evaluate a DRDA SECCHK command if it is sent. If the DRDA AR requests this
level of security but does not provide the correct information in the form
of a SECCHK command, this is a DRDA protocol error committed by the DRDA AR
implementation at the remote client.

For TCP/IP connections, this is a DRDA protocol error committed by the DRDA
AR implementation at the remote client.


I also have a trace that says it has had an MSACCESS sql err - sqlcode=-576
(no where to be found on mainframe db2 doc)
trace says sql err on "SELECT Config, nValue FROM MSysConf\ 0"

Has anyone else had this problem? If so, how do you fix it?

If not, can you point me in some direction to find doc on what I should be
looking for or a msg manual?

We have a contract person working on the pc side & he is unable to find the
doc we need.

He is trying to set this up for dynamic sql (if that makes any difference).

TIA, Marg









Leslie Pendlebury-Bowe

Re: need help with drda
(in response to Philip Gunning)
Marg
do you have DDF setup etc?

check VTAM as Phil suggests ..

ODBC into OS390 is a breeze normally .. PC -> ODBC -> TCP/IP -> WAN -
> OS390 TCP/IP -> DDF -> OS390 .

Leslie Pendlebury-Bowe
DB2 OS390 SAP


______________________________ Reply Separator _________________________________
Subject: Re: need help with drda
Author: Philip Gunning <[login to unmask email]> at Internet
Date: 11/30/99 5:25 PM


Marg, Sounds like you need to change the VTAM appl SECACPT DDF option to
ALREADYV. Have your VTAM person check this out. HTH Phil

Philip K. Gunning
DB2 DBA
IBM Certified Solutions Expert -- DB2 UDB
IBM Cetfified Solutions Expert -- CICS TS
Assoc List Owner

-----Original Message-----
From: Marg Gozdowski [SMTP:[login to unmask email]
Sent: Tuesday, November 30, 1999 1:29 PM
To: [login to unmask email]
Subject: need help with drda

Well, I am still trying to get the pc to mainframe working.

We are using Microsoft's ODBC Driver - tcp/ip setup - ar only - & on the
mainframe, we are getting err msg:

00D31050

Explanation: DB2 expected to receive security information for the remote
client but a valid DRDA SECCHK command was not received for a connection
request from a DRDA application requester (AR). The DRDA SECCHK command is
only valid with a DRDA security manager level greater than or equal to 5.

This can be the case if one of the following occurs:

The DRDA AR requested a DRDA security manager less than level 5 for an
SNA connection where the APPL command for DB2 was specified with the
SECACPT=NONE option.

The DRDA SECCHK command did not contain the required security
information.

The DRDA AR requested a DRDA security manager less than level 5 for a
TCP/IP connection.


This abend reason code is issued by the following CSECTs: DSNLTEXC,
DSNLTSEC.

System Action: The attempt to access the local database resource fails,
the
failure is reported to the SQL application, and the conversation is
terminated.

System Programmer Response: For SNA connections, if SECACPT=NONE and the
DRDA AR does not support DRDA security manager level 5, contact the DB2
security administrator to change the SECACPT option to ALREADYV or CONV to
allow for security information in the VTAM FMH-5 in the form of userid or
userid/password fields. If SECACPT=NONE is not changed, then DB2 cannot
accept connection requests from this remote client.

For SNA connections, If SECACPT=ALREADYV or CONV, then even though userid
or
userid/password fields in the FMH-5 are received and validated, DB2 must
evaluate a DRDA SECCHK command if it is sent. If the DRDA AR requests this
level of security but does not provide the correct information in the form
of a SECCHK command, this is a DRDA protocol error committed by the DRDA AR
implementation at the remote client.

For TCP/IP connections, this is a DRDA protocol error committed by the DRDA
AR implementation at the remote client.


I also have a trace that says it has had an MSACCESS sql err - sqlcode=-576
(no where to be found on mainframe db2 doc)
trace says sql err on "SELECT Config, nValue FROM MSysConf\ 0"

Has anyone else had this problem? If so, how do you fix it?

If not, can you point me in some direction to find doc on what I should be
looking for or a msg manual?

We have a contract person working on the pc side & he is unable to find the
doc we need.

He is trying to set this up for dynamic sql (if that makes any difference).

TIA, Marg














Sunil M Gupte

need help
(in response to Leslie Pendlebury-Bowe)
Hi

Can somebody please point out if following is allowed in DB2 ? (OS/390)
Specifically the Select in the Set statement ?

Update test1.tt1_cust_conv_stus
Set last_upd_user_id = userid,
bckt_id = (Select bckt_id
From test1.tt1_sub_prcs a,
Test1.tt1_bckt b
Where sub_prcs_nme = SUBPROCESS2
and b.bckt_nme = BUCKET2
and a.sub_prcs_id = b.sub_prcs_id)
where cms_id = CMSID

Thank u.

Sunil Gupte

Roger Miller

Re: need help
(in response to Sunil M Gupte)
That looks like it's fine with DB2 for OS/390 Version 6 and APAR PQ30383,
but not before.

Roger Miller



[login to unmask email]

Re: need help
(in response to Roger Miller)
Hi all,

If it is possible with DB2 V6 for OS390 APAR PQ30383 then doesn't it mean
that DB2 has to determine the relation among the tables(dynamically) and
then find out if only one record is qualifying for the result(used in the
SET clause) then only use it in the SET clause.
I am really wondering how is this possible.
Can somebody please explain me the way this query works and what is the
kind of relation three tables used should have.

Thanks for any help

Sanjeev




Sunil M Gupte <[login to unmask email]>@RYCI.COM> on 12/17/99
11:54:28 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: need help


Hi

Can somebody please point out if following is allowed in DB2 ? (OS/390)
Specifically the Select in the Set statement ?

Update test1.tt1_cust_conv_stus
Set last_upd_user_id = userid,
bckt_id = (Select bckt_id
From test1.tt1_sub_prcs a,
Test1.tt1_bckt b
Where sub_prcs_nme = SUBPROCESS2
and b.bckt_nme = BUCKET2
and a.sub_prcs_id = b.sub_prcs_id)
where cms_id = CMSID

Thank u.

Sunil Gupte



Mohammed Nayeem

Re: need help
(in response to ssethi@LOT.TATASTEEL.COM)
Hi Sunil

Yes ! a scalar subquery can be used in the SET clause
of an UPDATE statment in DB2 UDB , which can only
retrieve one value (i.e one row of one column).

regards

Nayeem

--- Sunil M Gupte <[login to unmask email]>
wrote:
> Hi
>
> Can somebody please point out if following is
> allowed in DB2 ? (OS/390)
> Specifically the Select in the Set statement ?
>
> Update test1.tt1_cust_conv_stus
> Set last_upd_user_id = userid,
> bckt_id = (Select bckt_id
> From test1.tt1_sub_prcs a,
> Test1.tt1_bckt b
> Where sub_prcs_nme = SUBPROCESS2
> and b.bckt_nme = BUCKET2
> and a.sub_prcs_id =
> b.sub_prcs_id)
> where cms_id = CMSID
>
> Thank u.
>
> Sunil Gupte
>

__________________________________________________
Do You Yahoo!?
Thousands of Stores. Millions of Products. All in one place.
Yahoo! Shopping: http://shopping.yahoo.com



[login to unmask email]

Re: need help
(in response to Michael Hannan)
Thanks Michael ,i was bit confused.U are right that anyway it checks for
the mutiple records and gives the error code(-811) if more than 1 record
exists and "=" instead of "IN" is specified. Regarding the relation there
would be one to many relationship(atleast) with the parent and child tables
,so primary/Unique key has to be specified in the subquery to make it
single record returnable.
As far as the original question is concern it gives syntax error in DB2 V5
for OS390.

Thanks again

Sanjeev





Michael Hannan <[login to unmask email]>@RYCI.COM> on 12/19/94 01:35:52 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: need help


Sanjeev,

Any subquery of form:

column = (subquery)
requires that the subquery return a single column and single row.
If multiple rows returned, the SQL terminates with an error code.

Exactly when the subquery is executed depends on whether correlated or not.

The SET clause would have to be executed in stage 2 processing after the
subquery is executed for each outer row found (if it is correlated to outer
query).

It appears from the SQL that table tt1_cust_conv_stus is related to
ttl_bckt
(probably the parent) via join column bckt_id. Similarly ttl_bckt is
related to tt1_sub_prcs (probably the parent) via join column sub_prcs_id.
This is evident in the SQL.

>From: [login to unmask email]
>Subject: Re: need help
>To: [login to unmask email]
>
>Hi all,
>
>If it is possible with DB2 V6 for OS390 APAR PQ30383 then doesn't it mean
>that DB2 has to determine the relation among the tables(dynamically) and
>then find out if only one record is qualifying for the result(used in the
>SET clause) then only use it in the SET clause.
>I am really wondering how is this possible.
>Can somebody please explain me the way this query works and what is the
>kind of relation three tables used should have.
>
>Thanks for any help
>
>Sanjeev
>
>
>
>
>Sunil M Gupte <[login to unmask email]>@RYCI.COM> on 12/17/99
>11:54:28 PM
>
>Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
>Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>
>
>To: [login to unmask email]
>cc:
>Subject: need help
>
>
>Hi
>
>Can somebody please point out if following is allowed in DB2 ? (OS/390)
>Specifically the Select in the Set statement ?
>
>Update test1.tt1_cust_conv_stus
>Set last_upd_user_id = userid,
> bckt_id = (Select bckt_id
> From test1.tt1_sub_prcs a,
> Test1.tt1_bckt b
> Where sub_prcs_nme = SUBPROCESS2
> and b.bckt_nme = BUCKET2
> and a.sub_prcs_id = b.sub_prcs_id)
> where cms_id = CMSID
>
>Thank u.
>
>Sunil Gupte