Solved

Dumping data from DB2 database

Posted on 2014-09-19
4
415 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DB2 process keep going heavy and memory usage high 13 1,368
DB2 Function LISTAGG failing with SQL0440N 1 1,165
DB2 error. 37 75
Another SQL with parm Part 2 44 68
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…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

930 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

11 Experts available now in Live!

Get 1:1 Help Now