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 and Linux remote server replication

I'm using DB2 10.5 and SLES 11 SP4.

My Question is, how would I replicate a remote server's db without having to ssh in and manually export/import.

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!

DB2: How to retain backups if automatic backup is enabled

Is there a way to retain the backups or configure the interval when they are deleted?According the manuals it's not possible: "If backup to disk is selected, the automatic backup feature will regularly delete backup images from the directory specified in the automatic database backup configuration. Only the most recent backup image will be available at any given time, regardless of the number of full backups that are specified in the automatic backup policy file."

Sounds strange to me.

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?

IBM DB2 9.7, force all connections of a specific database

I have a DB2 9.7 LUW instance with several databases. I am trying to take backup of a specific database, but Data Studio client reports that there is still an active connection so it fails.

How can I force the database to remove all active connections to that specific database? Thanks!

IBM DataStage Director is showing Error [duplicate]

This question is an exact duplicate of:

I am facing a strange problem suddenly.When I open the datastage director it gives the error THE CONNECTION IS BROKEN (81002).ScreenShot is attached for reference.All of the projects are running out of 10 projects only this issue is faced on only 1 project.I have googled a lot but no luck.I contacted the vendor but still issue persists.Your help will be highly appreciated.

Many Thanks,

Umar

enter image description here

Confused about database names and remote locations on AS400

I'm taked with buildig a web service that gets it's data from an old AS400 database server.

I'm trying to connect over nodejs using https://github.com/ibmdb/node-ibm_db and i'm getting this error on most databases.

SQL30061N The database alias or database name "database name here" was not found at the remote node. SQLSTATE=08004

Except from one, that states:An attempt to connect to a host failed due to a missing DB2 Connect product

(that's more or less expected)

I noticed that the last one is the *local database, and the rest have some other remote name.enter image description hereI also noticed that here it's stated that i can only connect to the *local database, but it never explains why http://www-03.ibm.com/systems/resources/systems_i_software_db2_pdf_db2connect.pdf

I'm really confused, all these databases are "local" in the sense they are all hosted in the same physical machine. what does *local mean on a remote location and why i cant connect to the other databases if they are not *local?

Command to list event monitors in DB2

I see the following commands:

  • drop event monitor
  • set event monitor
  • create event monitor

But nothing like list event monitor or get event monitor. Is there a DB2 command to list all current event monitors?

I'm currently using DB2 9.7 but am interested in answers for other versions as well.

Need DB2 OLEDB Driver / ODBC Driver [closed]

I am trying to establish a connection between excel and db2. I am able to successfully query and retrieve data from my DB.

I am using the below connection string

Provider=IBMDADB2;Database=RQAPROJ;HOSTNAME=jebal asu;PROTOCOL=TCPIP;PORT=50000;uid=administrator;pw d=we"

The issue now is that I have DB2 installed on my machine and the connection works perfect, however when I try from a Machine which does not have db2 installed I get an error straightaway that the Provider is not found.

I understand that DB2 drivers needs to be installed on the client machine as well.I had been trying in Google to get my hands on DB2 OLE DB drivers so that I can package it along with my excel tool but I am not able to find it for download.

I am not sure if there is any other way to get this done, so can anyone point me to a download location for these drivers?

Compiling PHP 5.5.7 with PDO IBM for IBM DB2

I'm trying to compile PHP 5.5.7 on an Ubuntu 12.04 with ubuntu precise partner repository enabled to allow to install IBM DB2 from db2exc package.

When I configure PHP with pdo ibm

source /home/db2inst1/sqllib/db2profile ./configure --prefix=/opt/php --enable-mbstring --enable-bcmath --enable-pcntl --enable-exif --enable-calendar --enable-opcache --enable-pdo --enable-sysvmsg --enable-sysvsem --enable-sysvshm --enable-wddx --with-curl --with-mcrypt --with-iconv --with-openssl --with-gettext=/usr --with-ibm_db2=/opt/ibm/db2/V9.7 --with-pdo-ibm=/home/db2inst1/sqllib

I get this WARNING:

configure: WARNING: unrecognized options: --with-pdo-ibm

http://php.net/manual/en/ref.pdo-ibm.php

After compile it, it don't works:

$conn = new PDO("ibm:DEVELOP", "db2inst1", "ibmdb2", [ PDO::ATTR_PERSISTENT => TRUE, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ] );

PHP Fatal error: Uncaught exception 'PDOException' with message 'could not find driver' in /home/vagrant/test/pdo_ibm.php:9

Using db2_connect works fine:

$conn = db2_connect('DEVELOP', 'db2inst1', 'ibmdb2');

$conn value:

resource(4) of type (DB2 Connection)

any help will be appreciated :)