Setting Up ODBC Connections to Mainframe For Data Server Driver Package Using db2cli

Philip Nelson

Setting Up ODBC Connections to Mainframe For Data Server Driver Package Using db2cli
I'm looking at setting up configurations to DB2 for z/OS from the IBM Data
Server Driver package. This is the client version which doesn't have CLP,
and so configuration is done with the db2cli command.

I can set up connections and DSNs that appear in the ODBC admin panels (and
in Excel).

But since we are going to DB2 for z/OS, we used to set up parameters to
restrict the amount of data being returned and limit (for example) the
table list to an individual database and / or table qualifier.

Looking at an export from an old client it looks like it has two parameters
set -

In a DB area there is a "DBName" parameter. And in the ODBC part there is
SchemaList parameter.

Looking at the db2cli command, I can see that you can specifiy a parameters
option on writecfg, and I've found that with this you can add <parameter>
sections uder your <dsn>.

I added the parameter "DBName" but it doesn't seem to filter out anything
at all as far as I can see.

And I think the SchemaList has to go into the db2cli.ini file ? But I
can't find any way of setting this up apart from a manual edit on this type
of client (on a regular client you'd use a "db2 update cli cfg" but on the
Data Server Driver package you don't have the DB2 CLP.

Can anyone give guidance on -

1) How to set up an ODBC DSN using db2cli which will only show me the
tables for one database and / or table qualifier on DB2 for z/OS ?
2) If needed, how to configure the db2cli.ini without just manually editing
it ?

Thanks

Phil

Ludovic Janssens

Setting Up ODBC Connections to Mainframe For Data Server Driver Package Using db2cli
(in response to Philip Nelson)
Hi Phil,


Normally, you should be able to select the driver parameters from the Windows ODBC admin panel. Make sure you have the correct one (32 or 64bit). If I remember well, you can edit the DSN and then click to edit the properties. You should get the extensive list.


db2cli.ini doesn't work in all circumstances. You'll probably have to look into the usage of the Db2dsdriver.cfg file; see https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.swg.im.dbclient.config.doc/doc/c0054555.html


Along with the db2dsdriver.cfg there is an xsd that can help you to work upon the parameters.


If you work with the parameters always check there exact meaning on the correct knowledge center reference, because similar parameters can have different meanings when working with CLI, ADO.NET or db2dsdriver.cfg.


Best regards,

Ludovic Janssens
Senior Consultant
[login to unmask email]<mailto:[login to unmask email]>
Mobile +32 492 25 10 73
[Twitter] https://twitter.com/ae_nv [LinkedIn] https://be.linkedin.com/company/ae-nv-sa

ae nv/sa * architects for business & ict * www.ae.be http://www.ae.be

Interleuvenlaan 27b, Researchpark Haasrode 1250, BE-3001 Heverlee
Tel +32 16 39 30 60, Fax +32 16 39 30 70, BTW BE0466550303

[AE] http://www.ae.be/ae-email-banner-redirect

________________________________
From: Philip Nelson <[login to unmask email]>
Sent: Wednesday, October 11, 2017 3:46:24 PM
To: [login to unmask email]
Subject: [DB2-L] - Setting Up ODBC Connections to Mainframe For Data Server Driver Package Using db2cli

I'm looking at setting up configurations to DB2 for z/OS from the IBM Data Server Driver package. This is the client version which doesn't have CLP, and so configuration is done with the db2cli command.

I can set up connections and DSNs that appear in the ODBC admin panels (and in Excel).

But since we are going to DB2 for z/OS, we used to set up parameters to restrict the amount of data being returned and limit (for example) the table list to an individual database and / or table qualifier.

Looking at an export from an old client it looks like it has two parameters set -

In a DB area there is a "DBName" parameter. And in the ODBC part there is SchemaList parameter.

Looking at the db2cli command, I can see that you can specifiy a parameters option on writecfg, and I've found that with this you can add <parameter> sections uder your <dsn>.

I added the parameter "DBName" but it doesn't seem to filter out anything at all as far as I can see.

And I think the SchemaList has to go into the db2cli.ini file ? But I can't find any way of setting this up apart from a manual edit on this type of client (on a regular client you'd use a "db2 update cli cfg" but on the Data Server Driver package you don't have the DB2 CLP.

Can anyone give guidance on -

1) How to set up an ODBC DSN using db2cli which will only show me the tables for one database and / or table qualifier on DB2 for z/OS ?
2) If needed, how to configure the db2cli.ini without just manually editing it ?

Thanks

Phil

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


AE-Disclaimer:

Dit bericht is vertrouwelijk en exclusief bestemd voor de geadresseerde ervan. Behoudens andersluidende expliciete afspraak is publicatie, gebruik of verspreiding ervan niet toegelaten. Gelieve de afzender onmiddellijk te verwittigen in geval van foutieve adressering of ontvangst en verwijder het bericht. Visies en opinies uitgedrukt in dit bericht zijn persoonlijk van de afzender tenzij aangegeven als zijnde van AE of verbonden ondernemingen.

This communication is intended for the addressee only and is privileged and confidential. Any unauthorized disclosure, copying, distribution or use is prohibited. If you have received this communication in error, please notify the sender immediately and destroy the original communication. Views and opinions are those of the sender unless clearly stated as being those of AE NV, its subsidiaries and/or associated companies.

Philip Nelson

Setting Up ODBC Connections to Mainframe For Data Server Driver Package Using db2cli
(in response to Ludovic Janssens)
I've got the setting up of db2dsdriver.cfg sorted I believe (you can do
this with db2cli.exe).

What I can't find is any scripted way of adding / removing entries from
db2cli.ini using the tools available with the Data Server Driver Package
(no DB2 CLP for example).

Phil

What I've not worked out

On 12 October 2017 at 07:41, Ludovic Janssens <[login to unmask email]> wrote:

> Hi Phil,
>
>
> Normally, you should be able to select the driver parameters from the
> Windows ODBC admin panel. Make sure you have the correct one (32 or 64bit).
> If I remember well, you can edit the DSN and then click to edit the
> properties. You should get the extensive list.
>
>
> db2cli.ini doesn't work in all circumstances. You'll probably have to look
> into the usage of the Db2dsdriver.cfg file; see https://www.ibm.com/
> support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.swg.im.
> dbclient.config.doc/doc/c0054555.html
>
>
> Along with the db2dsdriver.cfg there is an xsd that can help you to work
> upon the parameters.
>
>
> If you work with the parameters always check there exact meaning on the
> correct knowledge center reference, because similar parameters can have
> different meanings when working with CLI, ADO.NET or db2dsdriver.cfg.
>
>
> Best regards,
>
> *Ludovic Janssens*
> Senior Consultant
> [login to unmask email]
> Mobile +32 492 25 10 73 <+32%20492%2025%2010%2073>
> [image: Twitter] https://twitter.com/ae_nv [image: LinkedIn]
> https://be.linkedin.com/company/ae-nv-sa
>
> *ae *nv/sa n architects for business & ict n *www.ae.be*
> http://www.ae.be
>
> Interleuvenlaan 27b
> https://maps.google.com/?q=Interleuvenlaan+27b&entry=gmail&source=g ,
> Researchpark Haasrode 1250, BE-3001 Heverlee
> Tel +32 16 39 30 60 <+32%2016%2039%2030%2060>, Fax +32 16 39 30 70
> <+32%2016%2039%2030%2070>, BTW BE0466550303
>
> [image: AE] http://www.ae.be/ae-email-banner-redirect
> ------------------------------
> *From:* Philip Nelson <[login to unmask email]>
> *Sent:* Wednesday, October 11, 2017 3:46:24 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - Setting Up ODBC Connections to Mainframe For Data
> Server Driver Package Using db2cli
>
> I'm looking at setting up configurations to DB2 for z/OS from the IBM Data
> Server Driver package. This is the client version which doesn't have CLP,
> and so configuration is done with the db2cli command.
>
> I can set up connections and DSNs that appear in the ODBC admin panels
> (and in Excel).
>
> But since we are going to DB2 for z/OS, we used to set up parameters to
> restrict the amount of data being returned and limit (for example) the
> table list to an individual database and / or table qualifier.
>
> Looking at an export from an old client it looks like it has two
> parameters set -
>
> In a DB area there is a "DBName" parameter. And in the ODBC part there
> is SchemaList parameter.
>
> Looking at the db2cli command, I can see that you can specifiy a
> parameters option on writecfg, and I've found that with this you can add
> <parameter> sections uder your <dsn>.
>
> I added the parameter "DBName" but it doesn't seem to filter out anything
> at all as far as I can see.
>
> And I think the SchemaList has to go into the db2cli.ini file ? But I
> can't find any way of setting this up apart from a manual edit on this type
> of client (on a regular client you'd use a "db2 update cli cfg" but on the
> Data Server Driver package you don't have the DB2 CLP.
>
> Can anyone give guidance on -
>
> 1) How to set up an ODBC DSN using db2cli which will only show me the
> tables for one database and / or table qualifier on DB2 for z/OS ?
> 2) If needed, how to configure the db2cli.ini without just manually
> editing it ?
>
> Thanks
>
> Phil
>
> -----End Original Message-----
>
>
>
>
>
> *AE-Disclaimer: Dit bericht is vertrouwelijk en exclusief bestemd voor de
> geadresseerde ervan. Behoudens andersluidende expliciete afspraak is
> publicatie, gebruik of verspreiding ervan niet toegelaten. Gelieve de
> afzender onmiddellijk te verwittigen in geval van foutieve adressering of
> ontvangst en verwijder het bericht. Visies en opinies uitgedrukt in dit
> bericht zijn persoonlijk van de afzender tenzij aangegeven als zijnde van
> AE of verbonden ondernemingen. This communication is intended for the
> addressee only and is privileged and confidential. Any unauthorized
> disclosure, copying, distribution or use is prohibited. If you have
> received this communication in error, please notify the sender immediately
> and destroy the original communication. Views and opinions are those of the
> sender unless clearly stated as being those of AE NV, its subsidiaries
> and/or associated companies. *
>
> -----End Original Message-----
>

J&#248;rn Thyssen

RE: Setting Up ODBC Connections to Mainframe For Data Server Driver Package Using db2cli
(in response to Philip Nelson)

Hi Phil,

If you use db2dsdriver.cfg I don't think you need anything in db2cli.ini?

ODBC SchemaList maps to db2dsdriver.cfg SchemaFilter

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.swg.im.dbclient.config.doc/doc/r0054666.html

 

ODBC DBNAMe maps to db2dsdriver.cfg ZOSDBNameFilter

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.swg.im.dbclient.config.doc/doc/r0054681.html

 

My suggestion for rolling out Db2 Connect clients to workstations is:

1) If you have the unlimited connect license install it on the Db2 for z/OS side

2) Create a db2dsdriver.cfg file with the connections you need with the appropriate keywords. You can create multiple connections to the same database: PROD, PROD_EXCEL, PROD_ACCEL, etc. which have keywords set accordingly

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.swg.im.dbclient.config.doc/doc/c0054555.html

3) Run the Db2 Connect silent install; it can grap the db2dsdriver.cfg file you have just created

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.swg.im.dbclient.install.doc/doc/c0054554.html

4) Make a post-installation script that runs the db2cli/db2cli32 registerdsn commands to register ODBC connections from the db2dsdriver.cfg file

I normally follow this procedure to verify my db2dsdriver.cfg file, register for ODBC and test in excel:

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.swg.im.dbclient.install.doc/doc/r0058814.html

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

Philip Nelson

Setting Up ODBC Connections to Mainframe For Data Server Driver Package Using db2cli
(in response to Jørn Thyssen)
Thanks Jorn. Exactly what I needed ... all working now.

Phil

On 12 October 2017 at 16:43, Jørn Thyssen <[login to unmask email]> wrote:

> Hi Phil,
>
> If you use db2dsdriver.cfg I don't think you need anything in db2cli.ini?
>
> ODBC SchemaList maps to db2dsdriver.cfg SchemaFilter
>
> https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/
> com.ibm.swg.im.dbclient.config.doc/doc/r0054666.html
>
>
>
> ODBC DBNAMe maps to db2dsdriver.cfg ZOSDBNameFilter
>
> https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/
> com.ibm.swg.im.dbclient.config.doc/doc/r0054681.html
>
>
>
> My suggestion for rolling out Db2 Connect clients to workstations is:
>
> 1) If you have the unlimited connect license install it on the Db2 for
> z/OS side
>
> 2) Create a db2dsdriver.cfg file with the connections you need with the
> appropriate keywords. You can create multiple connections to the same
> database: PROD, PROD_EXCEL, PROD_ACCEL, etc. which have keywords set
> accordingly
>
> https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.
> 1.0/com.ibm.swg.im.dbclient.config.doc/doc/c0054555.html
>
> 3) Run the Db2 Connect silent install; it can grap the db2dsdriver.cfg
> file you have just created
>
> https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.
> 1.0/com.ibm.swg.im.dbclient.install.doc/doc/c0054554.html
>
> 4) Make a post-installation script that runs the db2cli/db2cli32
> registerdsn commands to register ODBC connections from the db2dsdriver.cfg
> file
>
> I normally follow this procedure to verify my db2dsdriver.cfg file,
> register for ODBC and test in excel:
>
> https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/
> com.ibm.swg.im.dbclient.install.doc/doc/r0058814.html
>
> Best regards,
>
> Jørn Thyssen
>
> Rocket Software
> 77 Fourth Avenue • Waltham, MA • 02451 • USA
> https://maps.google.com/?q=77+Fourth+Avenue%C2%A0%E2%80%A2+Waltham,+MA+%E2%80%A2+02451+%E2%80%A2+USA&entry=gmail&source=g
> E: [login to unmask email] • W: www.rocketsoftware.com
>
> Views are personal.
>
> -----End Original Message-----
>