Solved

Dumping data from DB2 database

Posted on 2014-09-19
4
407 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now