rogue NULLID.SYSSN200 package DB2 Datadriver 10.5

Art McEwen

rogue NULLID.SYSSN200 package DB2 Datadriver 10.5
Hi Listers,

We have a group of testers that is tripping over the above package when they connect to our test subsystem. The package still exists in our DB2 catalog but access has been revoked. Based on its age I think it's left over from DB2 Connect V8 (I don't have the package list from back then to know for sure).

The issue is they swear they've only got IBM Data Driver v10.5 installed on their client windows machine and screen shots from their program manager seems to back them up. That package isn't in the 10.5 bind list http://www-01.ibm.com/support/docview.wss?uid=swg21648351

I'm getting them to uninstall 10.5 and install 11.1 but it's still a head scratcher. Any ideas?

Target is DB2 z/os V11 nfm


Art McEwen

Sr DBA, Database & Mainframe Support
Health Solutions Delivery Br.
Health Services Cluster
4th flr, 49 Place d'Armes
Kingston ON K7L 5J3

[login to unmask email]<mailto:[login to unmask email]>

Office 613-548-6622
Cell 613-539-3903

Roy Boxwell

rogue NULLID.SYSSN200 package DB2 Datadriver 10.5
(in response to Art McEwen)
Syssn200 is for Text Search isnt it?

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 23 Nov 2017, at 17:50, McEwen, Art (MOHLTC) <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Hi Listers,

We have a group of testers that is tripping over the above package when they connect to our test subsystem. The package still exists in our DB2 catalog but access has been revoked. Based on its age I think it’s left over from DB2 Connect V8 (I don’t have the package list from back then to know for sure).

The issue is they swear they’ve only got IBM Data Driver v10.5 installed on their client windows machine and screen shots from their program manager seems to back them up. That package isn’t in the 10.5 bind list http://www-01.ibm.com/support/docview.wss?uid=swg21648351

I’m getting them to uninstall 10.5 and install 11.1 but it’s still a head scratcher. Any ideas?

Target is DB2 z/os V11 nfm


Art McEwen

Sr DBA, Database & Mainframe Support
Health Solutions Delivery Br.
Health Services Cluster
4th flr, 49 Place d'Armes
Kingston ON K7L 5J3

[login to unmask email]<mailto:[login to unmask email]>

Office 613-548-6622
Cell 613-539-3903


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

Roy Boxwell

rogue NULLID.SYSSN200 package DB2 Datadriver 10.5
(in response to Art McEwen)
Actually it is in 10.5 docu

http://www-01.ibm.com/support/docview.wss?uid=swg21648351

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 23 Nov 2017, at 17:50, McEwen, Art (MOHLTC) <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Hi Listers,

We have a group of testers that is tripping over the above package when they connect to our test subsystem. The package still exists in our DB2 catalog but access has been revoked. Based on its age I think it’s left over from DB2 Connect V8 (I don’t have the package list from back then to know for sure).

The issue is they swear they’ve only got IBM Data Driver v10.5 installed on their client windows machine and screen shots from their program manager seems to back them up. That package isn’t in the 10.5 bind list http://www-01.ibm.com/support/docview.wss?uid=swg21648351

I’m getting them to uninstall 10.5 and install 11.1 but it’s still a head scratcher. Any ideas?

Target is DB2 z/os V11 nfm


Art McEwen

Sr DBA, Database & Mainframe Support
Health Solutions Delivery Br.
Health Services Cluster
4th flr, 49 Place d'Armes
Kingston ON K7L 5J3

[login to unmask email]<mailto:[login to unmask email]>

Office 613-548-6622
Cell 613-539-3903


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

Art McEwen

RE: rogue NULLID.SYSSN200 package DB2 Datadriver 10.5
(in response to Roy Boxwell)

Sorry Roy are you saying it's in db2clipk.bnd?   Then I'm still confused, we have several apps using v9.x and v11.1 drivers and they're not tripping over this package..... hmm

 



In Reply to Roy Boxwell:

Actually it is in 10.5 docu

http://www-01.ibm.com/support/docview.wss?uid=swg21648351

Wolfgang Manus

RE: rogue NULLID.SYSSN200 package DB2 Datadriver 10.5
(in response to Art McEwen)

Hi Art,

what do you mean with "tripping", could you post an error message ?

The NULLID.SYS* are generic CLI packages, names vary by short/long and isolation level, but not by client version. The fact that some users succeed may indicate they use a different isolation level. Here's a good article around these CLI packages:

https://www.ibm.com/developerworks/community/blogs/IMSupport/entry/weekly_tips_from_db2_experts_db2_cli_packages_demystified?lang=en

I don't see why you want to revoke privileges on these to prevent outdated clients to connect. They should be granted to PUBLIC unless you want to enforce using different collections or disable remote connectivity.

You probably mix them up with the SQLxxx, ASNxxx, etc. NULLID packages for Command Line Processor (CLP) and tools like explain, replication, etc. which have different names per client version.

If you want to identify outdated DB2 clients/drivers you may need to analyze the SMF accounting records for the DISTSERV plan or look in your favourite online DB2 monitor. As an example, CA Sysview shows a field PRODUCT ID = SQL11011 for DB2 11.1 FP1 in the accounting details.

 

Best regards - Wolfgang


Art McEwen

RE: rogue NULLID.SYSSN200 package DB2 Datadriver 10.5
(in response to Wolfgang Manus)

Hi Wolfgang,

We set up a new test LPAR about a year ago and copied over all the NULLID packages from our existing LPAR at that time.   However we only granted public access to those created for DB2 Connect V9.x and above (along with the data driver packages).   Now this one application is getting -551 against this single package that was created in 2003.   I've checked the package list for V9.x and the datadrivers and that package isn't listed, I even redid the binds for V10.5 and v11 drivers and that package isn't included in the current drivers so I'm trying to determine if it's old code or the package is actually used by the current drivers.   The product ID shows SQL08xxxx so I think it's old code but I'm stumped as to where it's coming from.

 

Art


In Reply to Wolfgang Manus:

Hi Art,

what do you mean with "tripping", could you post an error message ?

The NULLID.SYS* are generic CLI packages, names vary by short/long and isolation level, but not by client version. The fact that some users succeed may indicate they use a different isolation level. Here's a good article around these CLI packages:

https://www.ibm.com/developerworks/community/blogs/IMSupport/entry/weekly_tips_from_db2_experts_db2_cli_packages_demystified?lang=en

I don't see why you want to revoke privileges on these to prevent outdated clients to connect. They should be granted to PUBLIC unless you want to enforce using different collections or disable remote connectivity.

You probably mix them up with the SQLxxx, ASNxxx, etc. NULLID packages for Command Line Processor (CLP) and tools like explain, replication, etc. which have different names per client version.

If you want to identify outdated DB2 clients/drivers you may need to analyze the SMF accounting records for the DISTSERV plan or look in your favourite online DB2 monitor. As an example, CA Sysview shows a field PRODUCT ID = SQL11011 for DB2 11.1 FP1 in the accounting details.

 

Best regards - Wolfgang


Michael Arlebrandt

RE: rogue NULLID.SYSSN200 package DB2 Datadriver 10.5
(in response to Art McEwen)

Hi Art,

regarding package NULLID.SYSSN200 I can confirm that it is a package used by CLI/OCBC and Java applications
For example I did a quick test today with a java program running with latest jcc driver and it was using SYSSN200

Those CLI packages as mentioned by Wolfgang have different flavours and this specific one is
S - Small package
N - Not with Hold
2 - ISOLATION(CS)

If you do a bind with a latest v11.1 client,
ref https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0007866.html,
ex in windows:
db2 bind "%DB2PATH%\bnd\@db2cli.lst" blocking all grant public

you'll get this SYSSN200 and some more that still are valid and needed.

And these CLI packages was also valid in v8 so you're probably right in that you have som old client/application using it

When I do reports from SMF accounting records for using my favorite DB2 monitor to track old clients I also collect

IP Name - to know the ip address of the server or client
Workstation - Name of the workstation or Server
Client - If it's DB2 LUW or JDBC Driver
Platform - Tells me if Win NT or other if known
Clientlevel - What level, yes there are many oldies from time to time at our customers
Product ID - SQLxxx or JCCxxxx
DB2 Userid(s) - Report which DB2s I find these in
Application(s) - and corresponding application or correlation id which gives a good hint

So by having a look in an SMF accounting report for IFCID 003 and PLAN DISTSERV you should be able to know who that v8 guy is.

best regards
Michael Arlebrandt
HCL Sweden

Wolfgang Manus

RE: rogue NULLID.SYSSN200 package DB2 Datadriver 10.5
(in response to Art McEwen)

Hi Art,

this package SYSSN200 is allocated by CLI applications that use isolation CS and run cursors specified WITHOUT HOLD (see naming conventions in the link below). It's bound from the db2clipk.bnd bind file. I've verified that on a V11 client, so this package is referenced in the latest DB2 client versions. It may have been originally created by a V8 client and not touched since then, but is still referenced in current versions, as these are generic packages for dynamic SQL.

The fact that the package has been created in V8 (indicated in PDSNAME column of SYSPACKAGE) doesn't necessarily mean it's not used in higher versions. If you free it and run a remote bind of the cli packages (db2 bind db2clipk.bnd grant public blocking all sqlerror continue) with a higher level client, it will be recreated, and you'll see current information in SYSIBM.SYSPACKAGE (e.g. PDSNAME referencing a newer client under which you initiated the bind).

You can copy it to a backup collection first before freeing it if you're worried so you can always restore it into the NULLID collection. If you have BINDADD granted to PUBLIC as well as CREATE IN on the NULLID collection, your application should even do an AUTOBIND of the missing package at runtime.

HTH - Wolfgang


Art McEwen

RE: rogue NULLID.SYSSN200 package DB2 Datadriver 10.5
(in response to Wolfgang Manus)

Thanks Wolfgang,

Unfortunately not knowing what packages are in db2clipk.bnd makes it hard to free them ahead of time.   I did an "action replace" and that rebound it (the fact that doesn't change the PDS name in syspackage is problematic to follow later).   Once I'm confident the package is current I'm happy granting public.

Thanks,


In Reply to Wolfgang Manus:

Hi Art,

this package SYSSN200 is allocated by CLI applications that use isolation CS and run cursors specified WITHOUT HOLD (see naming conventions in the link below). It's bound from the db2clipk.bnd bind file. I've verified that on a V11 client, so this package is referenced in the latest DB2 client versions. It may have been originally created by a V8 client and not touched since then, but is still referenced in current versions, as these are generic packages for dynamic SQL.

The fact that the package has been created in V8 (indicated in PDSNAME column of SYSPACKAGE) doesn't necessarily mean it's not used in higher versions. If you free it and run a remote bind of the cli packages (db2 bind db2clipk.bnd grant public blocking all sqlerror continue) with a higher level client, it will be recreated, and you'll see current information in SYSIBM.SYSPACKAGE (e.g. PDSNAME referencing a newer client under which you initiated the bind).

You can copy it to a backup collection first before freeing it if you're worried so you can always restore it into the NULLID collection. If you have BINDADD granted to PUBLIC as well as CREATE IN on the NULLID collection, your application should even do an AUTOBIND of the missing package at runtime.

HTH - Wolfgang


Kal Sub

RE: rogue NULLID.SYSSN200 package DB2 Datadriver 10.5
(in response to Art McEwen)

This link contains details of the CLI package naming conventions, including db2clipk. 

https://www.ibm.com/developerworks/data/library/techarticle/dm-0606chun/index.html

But generally I have found that binds from the client would automatically replace, or create if not present.

Regards

Kals


In Reply to Art McEwen:

Thanks Wolfgang,

Unfortunately not knowing what packages are in db2clipk.bnd makes it hard to free them ahead of time.   I did an "action replace" and that rebound it (the fact that doesn't change the PDS name in syspackage is problematic to follow later).   Once I'm confident the package is current I'm happy granting public.

Thanks,


In Reply to Wolfgang Manus:

Hi Art,

this package SYSSN200 is allocated by CLI applications that use isolation CS and run cursors specified WITHOUT HOLD (see naming conventions in the link below). It's bound from the db2clipk.bnd bind file. I've verified that on a V11 client, so this package is referenced in the latest DB2 client versions. It may have been originally created by a V8 client and not touched since then, but is still referenced in current versions, as these are generic packages for dynamic SQL.

The fact that the package has been created in V8 (indicated in PDSNAME column of SYSPACKAGE) doesn't necessarily mean it's not used in higher versions. If you free it and run a remote bind of the cli packages (db2 bind db2clipk.bnd grant public blocking all sqlerror continue) with a higher level client, it will be recreated, and you'll see current information in SYSIBM.SYSPACKAGE (e.g. PDSNAME referencing a newer client under which you initiated the bind).

You can copy it to a backup collection first before freeing it if you're worried so you can always restore it into the NULLID collection. If you have BINDADD granted to PUBLIC as well as CREATE IN on the NULLID collection, your application should even do an AUTOBIND of the missing package at runtime.

HTH - Wolfgang