Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Dumping data from DB2 database

Posted on 2014-09-19
4
434 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:Kent Olsen
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:
Kent Olsen 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:Kent Olsen
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

839 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