Articles & Content

ServerFault

ServerFault is a Q&A site for a community of system administrators and IT professionals

Note: The content of this blog is the opinion and thoughts of the blogger and does not necessarily represent the opinions of IDUG.

 

 
DB2 Integrity Checks and Exception Tables

I am working on planning a migration of a DB2 8.1 database from a horrible IBM encoding to UTF-8 to support further languages etc. I am encountering an issue that I am stuck on.

A few notes on this migration:

  1. We are using db2move to export and load the data and db2look to get the details fo the database (tablespaces, tables, keys etc).
  2. We found the loading process worked nicely with db2move import, however, the data takes 7 hours to load and this was unacceptable downtime when we actually complete the conversion on the main database.
  3. We are now using db2move load, which is much faster as it seems to simply throw the data in without integrity checks. Which leads to my current issue.

After completing the db2move load process, several tables are in a check pending state and require integrity checks. Integrity checks are done via the following:

set integrity for . immediate checked

This works for most tables, however, some tables give an error:

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:

SQL3603N Check data processing through the SET INTEGRITY statement has found integrity violation involving a constraint with name "blah.SQL120124110232400". SQLSTATE=23514

The internets tell me that the solution to this issue is to create an exception table based on the actual table and tell the SET INTEGRITY command to send any exceptions to that table (as below):

db2 create table blah_EXCEPTION like blahdb2 SET INTEGRITY FOR blah IMMEDIATE CHECKED FOR EXCEPTION IN blah USE blah_EXCEPTION

NOW, here is the specific issue I am having! The above forces all the rows with issues to the specified exception table. Well that's just super, buuuuuut I can not lose data in this conversion, its simply unacceptable. The internets and IBM has a vague description of sending the violations to the exception tables and then "dealing with the data" that is in the exception table. Unfortunately, I am not clear what this means and I was hoping that some wise individual knows and could help me out and let me know how I can retrieve this data from these tables and place the data in the original/proper table rather than these exception tables.

Let me know if you have any questions. Thanks!

Export Data from IBM DB2 into an SQL-INSERT Skript using IBM Data Studio Client or another tool

I have here a running IBM DB2 Database. I would like to export data from some tables into an SQL-Insert Skript, for example for the table T1 with the following content:

---------------| Col1 | Col2 | ---------------| 1 | Foo |---------------| 2 | Bar |---------------

A script like

INSERT INTO T1 (Col1, Col2) VALUES(1, 'Foo');INSERT INTO T1 (Col1, Col2) VALUES(2, 'Bar');

should be generated. The tables I would like to export do not have any auto-generated columns, so no special logic to treat those separately is necessary.

I've been using IBM Data Studio Client to export a DDL, examine the data, etc., but I did not find any export functions to export into an SQL-INSERT Script (there are functions to export into a CSV, etc.).

Can someone please give me some hints about a tool that could do this job, or tell me where in IBM Data Studio I could do this export?

Monitor SQL statements in DB2

I need to monitor SQL statements issued to DB2 database. I found the following article http://www.dba-db2.com/2010/01/trace-sql-statements-in-db2-database.html and I can to indeed capture SQL statements.

The problem is that prepared SQL statements still hold question marks. It there a way to get the final version of SQL statements?

DB2 version: 10.1.3

Thanks

How to connect to DB2 when the password ends with '!' in Windows

I am facing a problem to use the DB2 tools when using generic account with a generated password which ends with the Bang sign '!' to connect to DB2 database. I am not allowed to change the password because it is already used by other processes.

I know the user is valid and I can connect to the database with its credentials, but not from all db2 tools.

When using the Control Center it is okay.When using the Command Editor (GUI) or the Command Windows, I got this error message:

connect to WAREHOUS user administrator using !SQL0104N An unexpected token "!" was found following "". Expected tokens may include: "NEW". SQLSTATE=42601

Let's say that my password is: pass@!I am trying to use

c:\>db2 connect to sample user administrator using "pass@!"

or

c:\>db2 connect to sample user administrator using pass@!

And it both cases I got the same error message.

I could change the way I connect but it is not useful for me, for example:

c:\>db2 connect to sample user administratorEnter current password for administrator:

But I cannot use it from a batch file easily.

I would like to know how can I connect from the Command Editor, in order to use this user from the Graphical Tools.

BTW, I know that the Control Center is deprecated.

iSeries DB2 - SQL GUI Tool

Can someone suggest a good free Database tool with SQL support for connection to an iSeries. Currently I am using the green screen and it is quite frustrating.

How do I know when a new IBM DB2 Express-C version is released?

How do I know when a new IBM DB2 Express-C version (or security patch) is released? Please, don't suggest following #db2luw... :)

Update:

According to this page security patches (called "fix packs") can not be applied with Express-C:

DB2 fix packs can not be used with the Free version of DB2 Express-C. If you purchase the optional 12 Months Subscription license (also known as the Fixed Term License or FTL) you will be able to use DB2 Express with the FTL license and will be able to install the regular fix packs (Universal fix pack or Server fix pack).

Using binary from docker container on a host

I am trying to seed a [DB2] database running inside a docker container, with a script sitting on the [MacOS] host. My problem is that when I run the seeder on the host, it wants to execute db2 command/client, which I do not have.

Is there something that I could do, short of copying all of the install scripts from the host to the container?

I tried passing my file as tty stdin, yet it does not work. I get the below.

$ docker exec -i mydb2 /opt/ibm/db2/V11.5/bin/db2 < ./setupGSDB.sh SQL10007N Message "-1390" could not be retrieved. Reason code: "3".

While I did not look-up the exact error, the setupGSDB.sh uses some other scripts, which it expects to find locally. Hence, the error does make sense.

What could I do to export db2 to be accessible from the host? Do I have other options, in addition to "exporting" the db2 command or copying the setup scripts?

Thank you

Activation key part number for Db2 Data Server Client 11.5?

I think I have reached the end of Google, but still not able to find the part number I need to download the license file for IBM Data Server Client 11.5. Not listed here https://www.ibm.com/support/pages/db2-version-111-activation-key-part-numbers and I start to get tired of the message

LIC1407N You are trying to register an invalid license certificate file

Update

The client will be serving a set of .Net applications hosted on IIS. They are dependent on the db2 catalog konfig 'test' (and 'prod'). The db2 catalog commands is successfully, but when I want to verify the connection to db2 using command

db2 connect to TEST user TESTUSER

I get (when applying the valid password)

SQL1598N An attempt to connect to the database server failed because of alicensing problem. SQLSTATE=42968

If I type a password I know is wrong I get

D:\IBM\SQLLIB\BIN>db2 connect to TEST user TESTUSEREnter current password for CFFSAFI:SQL30082N Security processing failed with reason "24" ("USERNAME AND/ORPASSWORD INVALID"). SQLSTATE=08001

So the first error with correct password and the last one with the wrong, to my knowledge it confirmes that I'm able to connect to db2

Which is explained here https://www.ibm.com/support/pages/sql1598n-error-connecting-mainframe-db2-ibm-data-server-driver-even-though-db2-connect-license-file-exists-license-directory-install-path (I do not have a license file in the path)

D:\IBM\SQLLIB\BIN>db2licm.exe -lProduct name: "IBM Data Server Client"Product identifier: "db2client"Version information: "11.5"
LIC1435E I/O error when registering DB2 licence

We need a DB2 server for HCL Connections and I downloaded the installation and licence files from HCL Flexnet. The installation itself works, I could create a test database. But it's not possible to add our licence file:

[db2inst1@cnx65 ~]$ /opt/ibm/db2/V11.1/adm/db2licm -a /tmp/db2/licence/ese_u/db2/license/db2ese_u.licLIC1435E The license could not be added because of an I/O error.Explanation:You can perform basic license functions such as adding, removing,listing, or modifying licenses and policies by using the db2licmcommand.This message is returned when an error occurs while adding a licensebecause file and directory permissions or access settings do not allowthis action.User response:Perform the operation again. If the problem persists, contact IBMsoftware support. Related information: db2licm - License management tool command

The file /tmp/db2/licence/ese_u/db2/license/db2ese_u.lic seems to be a valid property file and the DB2 user owns it

$ ls -l /tmp/db2/licence/ese_u/db2/license/db2ese_u.lic-r--r--r-- 1 db2inst1 root 913 May 31 2016 /tmp/db2/licence/ese_u/db2/license/db2ese_u.lic
AS400 and Remote Commands

On the AS400 I want to remotely execute:

strpgrprg topgr(userx) message(ALERT!)

What options are available to me?

update: I want to run a AS400 program from outside of the AS400. I want to execute this command (or similar) from a windows batch, or a linux shell script.

I've found some info on how-to via FTP. Just haven't tried it out and still looking to see if it's the best way to do it.