Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    2

    Unanswered: Finding out the table and column name where a string is contained

    Hi guys,

    I know a string is coming out from the database (DB2), but I don't know which table and column(s) is it coming from.

    What's the query I can use to find that out?

    Thanks in advance.

    Jiang

  2. #2
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    You can talk to whom ever designed what ever is giving you the string. Other wise you are doomed
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Query all the data in your database, i.e. find all tables and all string columns, then query those columns to see if some value is the string you're looking for. And even that may not give you the desired answer if the string occurs in multiple tables or rows or in tables to which you have no direct access.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2009
    Posts
    2
    Using the information_schema, we can get all the tables and fields from the database. I probably can write a script to query all the fields to see if any of those contains that string...

    I am looking for an elegant way (one query) of doing this, and I have used such a query on mssql server...

    If you have such a query handy, could you please share it here?

    Thanks again,

    Jiang

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    This is crazy. You have no idea where the value is coming from and you are planning on querying the WHOLE db to find it?!?!

    You have to query not only string columns. You have to query EVERY column as any data type could have been converted to string. Heck, that string could have been hardcoded to begin with and did not come from any of your tables.

    I would think you have a better chance of winning a lotto tonight then finding this value.

    Have you considered doing what I recomended originally?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can't do that in a single query. And as Cougar says, there are a lot of scenarios where you will never find the row. Let's just assume that you have table X in your database. No user has SELECT privileges on that table - but some packages do. You invoke a stored procedure that executes statements in those packages and you get the value back. When you now try to query all the data in your database, you won't be able to access this table - you will not get the desired information. You have lost.

    So let's step back a little: why would you want to do something like that in the first place? Can you explain the reasoning?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    All previous comments would indeed also be my first answers to such a question.
    Another option could be to export or unload the whole bunch of tablespaces into a single file, then use some OS tools (like grep on Unix, or an editor) to search for the needed string, then try to make sense of the location of the hit(s).
    This may require a lot of disk storage, though ...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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