Hi,I've found a lot of threads regarding the use of JDBC Type4 and DB2 z/OS, but I couldn't find a defintive answer to myquestion.We have an application that runs on WebSphere ApplicationServer V6.1 AIX and connects to DB2 z/OS using the JDBC Driver Type4. We will move our application in the next months to a WAS Linux,DB2 will still remain on z/OS. We are now in the process ofdefining which software we need on the new linux machine, and weasked around about DB2 Connect. The answer we've got from a systemguy was, that WebSphere ships with DB2 JDBC Drivers and licenses,so we don't need to care about it. As far as I know, WAS comes witha Derby Driver that can connect to DB2, but it's not allowed to beused for DB2 z/OS.The options we discussed about where:a) buy a DB2 Connect License and put the files (drivers) on theserverb) copy the DB2 Drivers from the DB2 z/OS USS to the serverc) download the newest DB2 Driver and put it on theserverFor my understanding, only option a) follow the IBM directives.Can someone confirm it or point me to some good documentation aboutit?Thank you in advance!Rodney Krick. Hi Rodney,you are right: To access DB2 z/OS, you will need the respectiveDB2Connect license. Available licensing models are afaik z/OS serverbased(MSU) and User based, and presumably also Application serverbased(CPU).The JDBC driver (db2jcc.jar) requires an additional license filetoconnect to the target database system:- The standard license file is named 'db2jcclicensecu.jar'andonly allows for cloudscape and DB2 LUW connectivity- A proper DB2 Connect license will allow you to make use oftheadditional license file 'db2jcclicensecisuz.jar' ('cisuz' extendsthisto iSeries, SQLDS and z/OS connectivity)The license file must be deployed together with the JDBC driver.

Asanalternative, more recent versions of the driver (9.5 FP3 andabove,afaik) will allow you for server based license checking. Pete,thank you for your answer.

I still have a 'follow on':presumewe have a DB2 Connect license, so we are allowed to use the JDBCDriver Type 4 to connect to DB2 z/OS. Are we allowed NOT to use thedrivers from DB2 Connect and insted of that take the driversfrom the DB2 z/OS USS? The reason for that: we have also JavaBatches (JZOS) that run on zSeries and they use that drivers. Itwould be better for us, if we test the programs in theworkstation using the same drivers we have on the host, beforemoving the classes to z. OK, we could do all the tests on zOS butour Java Developers prefer to use eclipse to develop and debugthe routines and, when they are 'module tested' they send thepackage to the host and do the final tests.How are you guyshandling this situation? Thank you for any hints!Rodney-Suhner Peter schrieb: -An:login to unmask emailVon: Suhner Peter Datum: 11:41AMBetreff: DB2-L - RE: DB2 V9 z/OS DB2 Connect and JDBC DriverType 4Hi Rodney,?xml:namespace prefix = o ns ='urn:schemas-microsoft-com:office:office' / you are right: Toaccess DB2 z/OS, you will need the respective DB2 Connect license.Available licensing models are afaik z/OS server based (MSU) andUser based, and presumably also Application server based (CPU). TheJDBC driver (db2jcc.jar) requires an additional license file toconnect to the target databasesystem:- The standard licensefile is named 'db2jcclicensecu.jar' and only allows forcloudscape and DB2 LUWconnectivity- A proper DB2Connect license will allow you to make use of the additionallicense file 'db2jcclicensecisuz.jar' ('cisuz' extends this toiSeries, SQLDS and z/OS connectivity) The license file must bedeployed together with the JDBC driver.

As an alternative, morerecent versions of the driver (9.5 FP3 and above, afaik) will allowyou for server based license checking. This means that you canexecute some special program against the mainframe system toactivate a license compliance check directly on the z/OS serverside. This means no hassle with deploying license files, but alsoallowing anyone with a recent jdbc driver to be allowed to connectlicense-wise. Hope this helps,Pete From: Rodney Krick Sent: Tuesday, April 17, 2012 11:26 AMTo: login to unmask emailSubject: DB2-L - DB2 V9 z/OS DB2 Connect and JDBC Driver Type 4Hi,I've found a lot of threads regarding the use of JDBC Type4 and DB2 z/OS, but I couldn't find a defintive answer to myquestion.We have an application that runs on WebSphereApplication Server V6.1 AIX and connects to DB2 z/OS using the JDBCDriver Type 4.

We will move our application in the next months to aWAS Linux, DB2 will still remain on z/OS. We are now in the processof defining which software we need on the new linux machine, and weasked around about DB2 Connect. The answer we've got from a systemguy was, that WebSphere ships with DB2 JDBC Drivers and licenses,so we don't need to care about it.

Jul 13, 2017  IBM Data Server Driver 11.1 License File sam41.lic July 12, 2017 05:20 PM Hello, We've upgraded from DS Driver 10.5 to 11.1 and it appears that the license file now required is sam41.lic. It is working with that license, but I'm trying to confirm that this is the correct file so that we don't have any issues down the road (such as having a time. I am trying to access DB2 tables in a java project. I am able to access the tables when I manually added the jar files - db2jcc4.jar and db2jcclicensecisuz.jar. No issues in accessing the tables. But when I try to add these jar files through Maven, they won't add to the project.

As far as I know, WAS comes witha Derby Driver that can connect to DB2, but it's not allowed to beused for DB2 z/OS.The options we discussed about where:a) buy a DB2Connect License and put the files (drivers) on the serverb) copythe DB2 Drivers from the DB2 z/OS USS to the serverc) download thenewest DB2 Driver and put it on the serverFor myunderstanding, only option a) follow the IBM directives. Bt 1 bluetooth webcam 1.0.12 free download for mac. Cansomeone confirm it or point me to some good documentation aboutit?Thank you in advance!Rodney Krick -End OriginalMessage-Site Links: View post online View mailing listonline Send new post via email Unsubscribefrom this mailing list Manage yoursubscriptionThe slate for the 2012-2013 IDUG Board of Directors is nowavailable on IDUG.org for your review!Voting will close on May 13, 2012, and be announced at the IDUG DB2Tech Conference in DenverUse of this email content is governed by the terms of serviceat.

Hi Rodney,the USS part of z/OS is simply a posix compliant UNIX system, soIcannot see any issue to copy the drivers to a differentenvironment.While I don't know all the details, I do not expect that IBMprovideseparate drivers for the USS. The drivers are pure java stuff andtheyrun under any JVM as long as it is a supported java version.It is very simple to use the IBM JCC driver in trace mode bysimplyadding the respective parameter to the connect string. In thiscase, youcan trace the application traffic to and from your database invariouslevels of detail, and in any case, the trace file will containdetailson the driver version. This will allow you to easily comparedifferentjdbc driver releases you might be using in your environment.To activate trace, the connect string could look something likethis:'jdbc:db2://:/:traceLevel=899;traceFile=;'. Some more details on this (particularly tracelevels) canbe found here:Hope this helps,PeteFrom: Rodney Krick Sent: Wednesday, April 18, 2012 4:49 PMTo: login to unmask emailSubject: DB2-L - DB2 V9 z/OS DB2 Connect and JDBC Driver Type4Pete,thank you for your answer. I still have a 'follow on':presume we have a DB2 Connect license, so we are allowed to use theJDBCDriver Type 4 to connect to DB2 z/OS.

Are we allowed NOT to usethedrivers from DB2 Connect and insted of that take the drivers fromtheDB2 z/OS USS? The reason for that: we have also Java Batches (JZOS)thatrun on zSeries and they use that drivers. It would be better forus, ifwe test the programs in the workstation using the same drivers wehaveon the host, before moving the classes to z. OK, we could do allthetests on zOS but our Java Developers prefer to use eclipse todevelopand debug the routines and, when they are 'module tested' they sendthepackage to the host and do the final tests.How are you guys handling this situation?Thank you for any hints!Rodney-Suhner Peter schrieb: -An: login to unmask emailVon: Suhner Peter Datum: 11:41AMBetreff: DB2-L - RE: DB2 V9 z/OS DB2 Connect and JDBCDriverType 4Hi Rodney,you are right: To access DB2 z/OS, you will need the respectiveDB2 Connect license. Hi Rodney,We have a similar setup here with Websphere Application ServerV6.1, buton Windows. IBM support tells us that putting the DB2 Connectlicense(UE) on z/OS should work for WAS =without= having thedb2jcclicensecisuz.jar file in the classpath.

We can't get thattowork so far because we suspect that our older version of java 1.5.0withJDBC 3.63.75 is may not know how to determine if the license filecan beread from DB2 z/OS. We currently have an ETR open with IBM.In our discussion with IBM, they have mentioned that deploying theDB2Connect license file on z is not supported from z/OS USS. Theofficialline is that the license file must be deployed from the environmentthatyou are working on, i.e. Linux, UNIX or Windows. We have yet totrydeploying the license file from USS because it seems that itshouldwork. Keep in mind that DB2 Connect remains on your LUWenvironment.Only the license file resides in DB2 z/OS.Here is the link for activating the license file on DB2 z/OS:com.ibm.db2.luw.licensing.doc%2Fdoc%2Fr0057377.htmlI hope this sheds more light in a different way. I see that Petehasmore in-depth experience with JDBC and Java than I do.Bob PlataDAS - State Data Center503-378-4429Data Classification: Level 1 (Published)This electronic mail transmission contains information belonging totheDepartment of Administrative Service, State Data Center.

Thisinformation may be confidential and/or legally privileged andisintended for the use of the addressees designated above. If you arenotthe intended recipient, you are hereby notified that anydisclosure,copying, distribution or the taking of any action in reliance onthecontents of this electronic information is prohibited. If youhavereceived this transmission in error, please delete and notifyusimmediately.

State Policy 107-004-050 and DAS Policy107-01-180.From: Rodney Krick Sent: Tuesday, April 17, 2012 11:26 AMTo: login to unmask emailSubject: DB2-L - DB2 V9 z/OS DB2 Connect and JDBC Driver Type4Hi,I've found a lot of threads regarding the use of JDBC Type 4 andDB2z/OS, but I couldn't find a defintive answer to my question.We have an application that runs on WebSphere Application ServerV6.1AIX and connects to DB2 z/OS using the JDBC Driver Type 4. We willmoveour application in the next months to a WAS Linux, DB2 will stillremainon z/OS. We are now in the process of defining which software weneed onthe new linux machine, and we asked around about DB2 Connect. Theanswerwe've got from a system guy was, that WebSphere ships with DB2JDBCDrivers and licenses, so we don't need to care about it. As far asIknow, WAS comes with a Derby Driver that can connect to DB2, butit'snot allowed to be used for DB2 z/OS.The options we discussed about where:a) buy a DB2 Connect License and put the files (drivers) on theserverb) copy the DB2 Drivers from the DB2 z/OS USS to the serverc) download the newest DB2 Driver and put it on the serverFor my understanding, only option a) follow the IBM directives.Cansomeone confirm it or point me to some good documentation aboutit?Thank you in advance!Rodney Krick-End Original Message-End Original Message.

The JDBC driver out on the USS filesystems comes with DB2 forz/OS.The JDBC driver for Unix and Windows comes from the DB2 connectproduct and requires the appropriate licenses.They are materially different, certainly from a licensingperspective. I would not assume that the z/OS USS JDBC driver wouldwork anywhere else; remember it has to deal with the half asciihalf EBCDIC complexity of USS.The USS guy is the one that would be involved with servicing JDBCrequests from z/OS batch jobs invoking JAVA.The JDBC drivers that come with WebSphere tend to be very out ofdate relative to current levels of the platform appropriate JDBCdriver. I don't believe WebSphere automatically ships the cisuz.jarfile needed to activate z/OS connectivity; I could be wrong sinceits been around a year since I last looked at this question.Edward Long- On Wed, 4/18/12, Suhner Peter wrote:From: Suhner Peter Subject: DB2-L - RE: DB2 V9 z/OS DB2 Connect and JDBC DriverType 4To: login to unmask emailDate: Wednesday, April 18, 2012, 11:42 AMHi Rodney, the USS part of z/OS is simply a posix compliantUNIX system, so I cannot see any issue to copy the drivers to adifferent environment. While I don't know all the details, I do notexpect that IBM provide separate drivers for the USS. The driversare pure java stuff and they run under any JVM as long as it is asupported java version. It is very simple to use the IBM JCCdriver in trace mode by simply adding the respective parameter tothe connect string. In this case, you can trace the applicationtraffic to and from your database in various levels of detail, andin any case, the trace file will contain details on the driverversion.

This will allow you to easily compare different jdbcdriver releases you might be using in your environment. Toactivate trace, the connect string could look something like this:'jdbc:db2://:/:traceLevel=899;traceFile=;'.Some more details on this (particularlytrace levels) can be found here:Hope this helps,Pete From: Rodney Krick Sent: Wednesday, April 18, 2012 4:49 PMTo: login to unmask emailSubject: DB2-L - DB2 V9 z/OS DB2 Connect and JDBC Driver Type 4Pete,thank you for your answer. I still have a 'follow on':presume we have a DB2 Connect license, so we are allowed to use theJDBC Driver Type 4 to connect to DB2 z/OS. Are we allowed NOT touse the drivers from DB2 Connect and insted of that take thedrivers from the DB2 z/OS USS? The reason for that: we have alsoJava Batches (JZOS) that run on zSeries and they use that drivers.It would be better for us, if we test the programs in theworkstation using the same drivers we have on the host, beforemoving the classes to z. OK, we could do all the tests on zOS butour Java Developers prefer to use eclipse to develop and debugthe routines and, when they are 'module tested' they send thepackage to the host and do the final tests. How are you guyshandling this situation? Thank you for any hints!Rodney-Suhner Peter schrieb: - An:login to unmask emailVon: Suhner Peter Datum: 11:41AMBetreff: DB2-L - RE: DB2 V9 z/OS DB2 Connect and JDBC DriverType 4Hi Rodney, you are right: To access DB2 z/OS, you willneed the respective DB2 Connect license.

Available licensing modelsare afaik z/OS server based (MSU) and User based, and presumablyalso Application server based (CPU). The JDBC driver(db2jcc.jar) requires an additional license file to connect to thetarget database system:- Thestandard license file is named 'db2jcclicensecu.jar' and onlyallows for cloudscape and DB2 LUWconnectivity- A proper DB2Connect license will allow you to make use of the additionallicense file 'db2jcclicensecisuz.jar' ('cisuz' extends this toiSeries, SQLDS and z/OS connectivity) The license file mustbe deployed together with the JDBC driver. As an alternative, morerecent versions of the driver (9.5 FP3 and above, afaik) will allowyou for server based license checking. This means that you canexecute some specialprogram against the mainframe system to activate a licensecompliance check directly on the z/OS server side. This means nohassle with deploying license files, but also allowing anyone witha recent jdbc driver to be allowed to connect license-wise.Hope this helps,Pete From: Rodney KrickSent: Tuesday, April 17, 2012 11:26 AMTo: login to unmask emailSubject: DB2-L - DB2 V9 z/OS DB2 Connect and JDBC Driver Type 4Hi,I've found a lot of threads regarding the use ofJDBC Type 4 and DB2 z/OS, but I couldn't find a defintive answer tomy question.We have an application that runs on WebSphereApplication Server V6.1 AIX and connects to DB2 z/OS using the JDBCDriver Type 4.

We will move our application in the next months to aWAS Linux, DB2 will still remain on z/OS. We are now in the processof defining which software we need on the new linux machine, and weasked around about DB2 Connect.

The answer we've got from a systemguy was, that WebSphere ships with DB2 JDBC Drivers and licenses,so we don't need to care about it. As far as I know, WAS comes witha Derby Driver that can connect to DB2, but it's not allowed to beused for DB2 z/OS.The options we discussed about where:a) buy a DB2Connect License and put the files (drivers) on the serverb) copythe DB2 Drivers from the DB2 z/OS USS to theserverc) download the newest DB2 Driver and put it on theserverFor my understanding, only option a) follow the IBMdirectives. Can someone confirm it or point me to some gooddocumentation about it?Thank you in advance!Rodney Krick-End Original Message- -End OriginalMessage- -End Original Message-Site Links: View post online View mailing listonline Send new post via email Unsubscribefrom this mailing list Manage yoursubscriptionThe slate for the 2012-2013 IDUG Board of Directors is nowavailable on IDUG.org for your review!Voting will close on May 13, 2012, and be announced at the IDUG DB2Tech Conference in DenverUse of this email content is governed by the terms of serviceat.

From a licensing perspective, you definitely need the DB2Connectlicense, and the db2jcclicense cisuz.jar file is required totechnically allow the connection (unless you activate the serversidelicense check mechanism).From a technical perspective, remember that Java is byte code andalwaysruns inside a Java Virtual Machine. One of the greatest pros ofJavacode is platform independence, basically allowing Java programs torunon any platform for which a JVM exists. The JVM interprets the Javacodeand provides the required level of OS abstraction, with thedownside ofJava being more resource intensive than code which is directlycompiledto run on a specific platform, like e.g. Cobol, C, PL/1,Assembler,etc.The IBM JCC driver is pure Java and therefore also independent oftheoperating system. Any version of the driver implements access toallsupported target platforms, and therefore EBCDIC/ASCII translationisbuilt into the driver. It is only the separate license file whichallowsor denies access to specific targets. If you activate the drivertrace(as explained earlier), it will also show you the blocks ofdatatransferred between the driver and DB2 in both, EBCDIC and ASCII,nicelylaid out for direct comparison.

You can easily test this by takingadb2jcc.jar file from wherever (e.g. From your Windows package, fromyourUSS.) and run it on any other platform - it will workproperly.Regards,PeteFrom: Ed Long Sent: Wednesday, April 18, 2012 6:33 PMTo: login to unmask emailSubject: DB2-L - RE: DB2 V9 z/OS DB2 Connect and JDBC DriverType 4The JDBC driver out on the USS filesystems comes with DB2 forz/OS.The JDBC driver for Unix and Windows comes from the DB2 connectproductand requires the appropriate licenses.They are materially different, certainly from a licensingperspective.

Provides support for creating read-only Hive external tables that canread the results of a query run on the Analytics Accelerator attached to DB2 for z/OS.

Download

• Download the DB2 JDBC driver and extract the files db2jcc4.jar and db2jcc_license_cisuz.jar
• Download the latest hive-jdbc-storage-handler-*.jar from this project's releases

Usage

• Tested with Horton Data Platform (HDP) 2.1, Hive 0.13.0

• Start Hive CLP (type 'hive' in a command line window) and add the JAR files to the classpath using the following commands:

• Still in CLP, you can now create an external JDBC table in Hive:

Configuration

Table Creation

Required Table Properties

• qubit.sql.database.type - Currently supports MYSQL, H2, DERBY and DB2Z.
• qubit.sql.jdbc.url - Full JDBC connection URL.
• qubit.sql.jdbc.driver - JDBC driver class name.
• qubit.sql.query - Query to run against the database. The Storagehandler is intelligent enough to determine the correct conditions toapply to the database query based on the hive query being executed.As such, please ensure that there are no WHERE clauses definedhere. If column names differ between the hive table and the query,use the qubit.sql.column.mapping property to define the mappings.

Optional Table Properties

• qubit.sql.column.mapping - If the Hive column names are differentfrom the corresponding table column, use this property to indicateit. Format is: hiveColumnName=dbColumnName[:type] separated bycommas. Currently the only accepted type is date. Sample usage:'qubit.sql.column.mapping' = 'id=part_id, created=created_datetime:date'.Only the columns that differ need to be specified.
• qubit.sql.jdbc.fetch.size - Resultset fetch size. Default value is1000.
• Connections to the database are made through a DBCP connection pool.Specify any DBCP configurationoptions byprefixing them with qubit.sql.dbcp.. Eg:qubit.sql.dbcp.maxActive=5

Querying

The Storage handler is intelligent enough to determine the correctconditions to apply to the database query based on the hive query beingexecuted. Therefore no additional steps need to be performed whilequerying.

Adding a WHERE-clause to qubit.sql.query will disable predicate push-downfrom the Hive query. The query will be run as-is.

Resultset fetch size (the default is 1000 rows) can be changed at query time:

Building

• Clone the git project and run mvn clean package to produce the storagehandler JAR.