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:
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!
How to track db2 database changes from linux without triggers or db modification
I've got an assignment where I need to create a shell script to track just the changes to the employees table (DB2 database) record for any insert, delete or update that the HR department perform to users hourly.
No need for SQL or bash code. Just ideas on how to get this done.
Cons: I cannot edit or alter any DB schema or add/create any trigger.
Pros: I have the credentials to select * from the table
Is there any way I can achieve this without bringing all db records and compare them?
I just only need the new changes (update, insert or delete).
PS: I DO have a successful DB connection and already performing select queries.
Thanks for the time to look at the post.
Unable to install php_ibm_db2.dll on PHP 5.6
I'm getting the following error when trying to start Apache. I've confirmed the extension is in the folder and other extensions such as
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
A script like
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?
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.
How to do REORG command on DB2
I want to reorganize(offline) some db2 tables.Also I want to executing runstats command after reorg command.
I have a question.
Do I need to stop db2 database or deactivate or both of them before executing reorg command?
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
AS400 and Remote Commands
On the AS400 I want to remotely execute:
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.
ansible - Run "command db2 update database manager configuration using svcename db2c_db2inst1" as db2 user
I am setting up ansible to install a DB2 on a linux server. Everything is working except in the last step I need to run:
However, I cannot seem to run that as a unprivileged user (I can run it as db2isnt1 user from cmd line and it works). The task I am using looks like this:
but I get the following error:
Drop database on DB2 9.5 - SQL1035N The database is currently in use
I've never got this working the first time, but now I can't seem to do i at all.
There is a connection pool somewhere using the database, so trying to drop the database when an application is using the database should give this error. The problem is there are no connection to the database when I issue these commands:
This allways give:
running this command shows no connections/applications
I can even deactivate the database, but still can't drop it.
Anyone got any clues? I'm running the cmd-windows as the local administrator (windows 2008) and this is also the admin for DB2. The connectionpool-user cannot connect during quiesce-state.