Solved

Dumping data from DB2 database

Posted on 2014-09-19
4
428 Views
Last Modified: 2014-09-19
Hello,

I need to check if a piece of data (a password) is present anywhere in a DB2 database. Seems like the easiest way to do that is to dump the database to plain text (similar to mysqldump) and then grep the resulting file. However I can't find any easy way to dump the whole database. Any suggestions?

I know how to dump individual tables with export command, however, since there are quite a lot of tables, it will take a while.

Alternatively if there is a way to a full text search on the whole database (all tables), this will solve my problem too.

Thanks.
0
Comment
Question by:gremwell
  • 3
4 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 40332416
Hi Grem,

If you're on a *nix system you can run grep on the full backup.  The results won't be particularly meaningful, except for a pass/fail (yes/no) on the string occurring.  Using a regular expression will reveal if the exact string exists.  Otherwise, you'll get a "yes" if the string is a substring.

How many tables and about how much data do you have?  A bit of meta-sql go generate a query for all tables is pretty easy, but if could take quite a while to search the entire database!


Kent
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 40332455
If you're running LUW, the following code will generate the SQL to test the entire database.  If you have a lot of columns/data, this is probably not a good idea.

SELECT 
  'SELECT ''' || table_name || '.' || column_name || ''', count(*) FROM ' || rtrim (table_schema) || '.' ||
  table_name || ' WHERE ' || column_name || ' = ''{insert string here}'';'
FROM sysibm.columns
WHERE table_schema not like 'SYS%'
  AND data_type like 'CHAR%'
  AND character_maximum_length > 8
FETCH FIRST 10 rows only;

Open in new window


Replace {insert string here} with the string that you want to find.  In the next to last line, replace 8 with the length of the search string.  That will cause the search to skip columns too narrow to hold the target string.


Kent
0
 
LVL 3

Author Comment

by:gremwell
ID: 40332481
Hello Kent,

Thanks for your comment. I have came up with a solution in a meantime. First I made a list of tables and dumped them to a file:
export to /tmp/tables.txt of del select tabname from syscat.tables where type='T' and owner='MYUSER';

Then I used the list to generate an SQL script that did
export to /tmp/dbdump/<tablename> of del select * from <tablename>
for each table

Then I ran the script with clpplus and grepped the resulting files.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40332494
Cool.  That should work fine.  

Remember to use the correct regex to search for the password or you could get a lot of false positives.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how can I create a record layout for this file? 14 452
SQL0338 Error received on query 3 292
sql is not working correctly 23 360
SAS Email Chart as attachment code 2 288
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question