Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2013
    Posts
    4

    Unanswered: SQL statement to obfuscate phone nums in DB2

    I am new to DB2 Express-C and am wondering if a SQL statement can be run in DB2 that can go thru all tables and change (could be scramble, replace, random, whatever) any 10-dgit phone numbers found. In a nut shell, do a global search and replace on all fields (that contain phone nums), in all tables, to obfuscate any phone numbers found (private info).

    I need to hand off a client's DB2 database (17 tables) to a software vendor to do some reporting but I have been tasked with making sure NO real phone numbers get exposed in the process. I'm sure there is a SQL statement that could handle a single table (then I could go into DB2 Control Center and run it 17x) but I am looking for a quick, clean way to 'prep' this DB before sending it out.

    Thanks in advance to you DB2 gurus!

    Mike

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Are you unloading the data to csv file for sending? You could just select 000-000-0000, instead of the column. There is, also, the translate function where you could change each number to another number, but there is no garauntee that you won't change one number to a real number from your database, unless, you threw in some alpha characters. I like the first option better though.

  3. #3
    Join Date
    Aug 2013
    Posts
    4

    No export just entire DB

    No CSV, I am actually planning on zipping up the entire NODE000 (incl sub-directories) so that the report creator can run it straight away in DB2.

    Quote Originally Posted by dav1mo View Post
    Are you unloading the data to csv file for sending? You could just select 000-000-0000, instead of the column. There is, also, the translate function where you could change each number to another number, but there is no garauntee that you won't change one number to a real number from your database, unless, you threw in some alpha characters. I like the first option better though.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by mem5449 View Post
    No CSV, I am actually planning on zipping up the entire NODE000 (incl sub-directories) so that the report creator can run it straight away in DB2.
    This is not the way to create a copy of the DB2 database. I suggest you use the BACKUP command to create the database image.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Aug 2013
    Posts
    4

    Making a backup

    Thanks for the tip. I am going to try doing that. BTW, does it create one file when it's done or a directory structure? Again, I'm new to DB2 and still learning how to use DB2 Control Center.

    When I first needed a working copy of my client's database, I simply copied the NODE000 directory to my PC and I was able to use it just fine from DB2 Control Center AND also from my development tool (Rad Studio).

    In any case that concern is secondary. The database has to be 'cleansed' (obfuscated) before it goes anywhere. Any suggestions on the proper SQL statement to accomplish that??

    Thanks.

    Quote Originally Posted by n_i View Post
    This is not the way to create a copy of the DB2 database. I suggest you use the BACKUP command to create the database image.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by mem5449 View Post
    Any suggestions on the proper SQL statement to accomplish that??
    You need to first specify in greater detail what is it you want. A computer cannot magically guess what tables and columns may contain a telephone number, or what constitutes a telephone number for that matter. This part has nothing to do with DB2, it's purely common sense. Suppose you're not writing a SQL statement but explaining the task to a "mechanical turk".
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Aug 2013
    Posts
    4

    Another try..

    Thanks for your response. I actually thought my first post was rather clear.. "can DB2 SQL statement go thru all tables and change any 10-digit phone numbers found". Starting to appear [from your questions] that SQL statement CAN NOT.

    Since no one has come back with a definitive answer like "Yes, a DB2 statement COULD do this provided a list of table names and field names (those that contain phone numbers) were provided", let me make another attempt at being clearer..

    1. What constitutes a phone number? Any field that has just a 10 digit number in it (stated in earlier post). Note: this DB is a call log and there is no possibility of a 10-digot value [in a field] being confused with any OTHER type of data
    2. What I want? Basically, a Yes or No on whether a DB2 SQL statement can, in simplest terms, perform global search-n-replace on any data value that is a phone number. Also, what that statement might look like.
    3. Replacement value can be ANY DIFFERENT number (placeholder). Note: Although the same number could be used throughout, I'd prefer to use some method for diversity such as maybe reversing the number (215-555-1212 becomes 2121555512), or Random, or Scrambled. You get the idea.
    4. Yes, a detailed list of tables and fields can be provided IF an SQL statement CAN accomplish the task mentioned above and in first post.

    NOTE: if this DB can be 'cleansed' first, then the reporting project will be posted on GURU dot com and someone who knows DB2 and SQL could make some extra cash$$.

    Still unanswered from previous post: "Does DB2 backup command/procedure produce single file or directory structure?"

    Quote Originally Posted by n_i View Post
    You need to first specify in greater detail what is it you want. A computer cannot magically guess what tables and columns may contain a telephone number, or what constitutes a telephone number for that matter. This part has nothing to do with DB2, it's purely common sense. Suppose you're not writing a SQL statement but explaining the task to a "mechanical turk".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •