Optim - Test Data Management and Archiving

Expand all | Collapse all

Query Archives without DBVisualizer

  • 1.  Query Archives without DBVisualizer

    Posted Tue March 24, 2020 03:54 AM
    Has anyone in the IBM Community used something other than DBVisualizer to query the archives? Primarily we use it to extract BLOBs into separate files. Was wondering if anyone had success using any other product.

    Thanks!
    Donna Simmons
    Wells Fargo, Applications Systems Engineer




    ------------------------------
    Donna Simmons
    ------------------------------


  • 2.  RE: Query Archives without DBVisualizer

    Posted Tue March 24, 2020 10:53 AM
    I'm a fan of AQT Advanced Query Tool. http://querytool.com/index.html It also comes in handy as a multi-database tool. JDBC/ODBC File DSN, Weird Direct Connect stuff, Etc. The v10 with extended features is awesome.

    ------------------------------
    Danny Lankford
    3M - IT Manager
    ------------------------------



  • 3.  RE: Query Archives without DBVisualizer

    Posted Tue March 24, 2020 12:02 PM

    Thanks Danny! I will definitely take a look at this one. Quick question: Can this product be setup to create separate files for each BLOB in a record?






  • 4.  RE: Query Archives without DBVisualizer

    Posted Tue March 24, 2020 12:47 PM
    Yes. http://querytool.com/features/tour4.html#lobs look at the features link. you can export *LOBs to files or view them right in the AQT tool. pretty handy. There's some cool export functions to script the exports as well.

    ------------------------------
    Danny Lankford
    3M - IT Manager
    ------------------------------



  • 5.  RE: Query Archives without DBVisualizer

    Posted Tue March 24, 2020 02:41 PM
    ​Impressive! I don't believe DBVisualizer offered the ability to view LOBs in their tool. I'm reviewing the AQT Feature comparison list and it looks like the Display Data, including LOB columns is part of the Standard version. Thanks for the suggestion!

    ------------------------------
    Donna Simmons
    Application System Engineer
    ------------------------------



  • 6.  RE: Query Archives without DBVisualizer

    Posted Thu March 26, 2020 03:30 AM
    Not sure if I understand the question / thread correctly, but DBVisualizer can view LOBs in their tool ; just do a normal select (that can include LOB fields) , and for a record, double click on the LOB field, and it will 'try' and make it readable. Also, when f.i. exporting the full result set to an excel format, it will make the LOB's readable in the generated excel sheet.

    ------------------------------
    Erwin Hattingh
    Systems Engineer / Db2 DBA
    Triodos Bank
    ------------------------------



  • 7.  RE: Query Archives without DBVisualizer

    Posted Thu March 26, 2020 02:31 PM

    Hi Erwin! My first question was to find an alternative to DBVisualizer to query the archives. You make a good point that DBVisualizer can display LOB information and it will do its best to make it readable. The feature Danny shared in his post was that AQT can open the image file (if that is what is in the LOB) in the tool also. http://querytool.com/features/tour4.html#lobs

     

    Another feature I used in DBVisualizer was the ability to export LOBs to separate files. AQT has same feature http://querytool.com/features/tourexport.html and also offers a linked-to in the main export results. They have an example of how the linked-to feature works here http://www.querytool.com/photos.html.



    ------------------------------
    Donna Simmons
    Application System Engineer
    ------------------------------



  • 8.  RE: Query Archives without DBVisualizer

    Posted Fri April 17, 2020 07:34 PM
    Danny,

    Thanks for recommending AQT. Seems like a solid piece of software. I was able to create an ODBC connection and query a test archive file! 

    Quick question: In the ​Database Object pane I see my DefTdpName name. Below that name are two subcategories; Table and Views and System Tables. However they don't expand. I was hoping I would expand and see the table names in the archive file displayed in the GUI. Does your setup allow you to see the tables in the archive? This isn't a deal breaker but was curious if there was a setting that I needed to flip.

    Thanks again,
    Donna Simmons
    Application System Engineer

    ------------------------------
    Donna Simmons
    Application System Engineer
    ------------------------------



  • 9.  RE: Query Archives without DBVisualizer

    Posted Tue April 21, 2020 09:03 AM

    HI Donna,

     

    No tree expansion twisty. When an object is selected in the pane, it becomes expanded on the window to the right.

    I don't believe that there is a config setting to change this behavior to look like say... SQL server client, MS SQL Studio Management client.


    See the screen capture.

     

     

    Danny L. Lankford | IT Manager

     






  • 10.  RE: Query Archives without DBVisualizer

    Posted Tue April 21, 2020 03:00 PM
    ​Thanks Danny for the picture! I confirmed I see the objects for database connections. If I choose an archive connection nothing appears under tables and views. Is that your experience too? DBVis can display the tables and column information inside of an archive file which is a nice feature.

    Thanks again for recommending AQT!

    ------------------------------
    Donna Simmons
    Application System Engineer
    ------------------------------



  • 11.  RE: Query Archives without DBVisualizer

    Posted Thu April 23, 2020 01:00 PM
    ​Danny,

    Not sure if you have ever run into this but looks like AQT won't export CLOBs from an archive.

    This is the exact error message.
    Export failed with error during Fetch
     S1000(31)[IBM][Optim Connect Driver for ODBC 3.5]The length of the buffer (%d) is less than of the received data chunk (%d)
     (1.11 secs)

    I used www.lipsum.com to generate a 21,000 byte text block and inserted it into an Oracle test table. AQT had no issue with an export from the table. I confirmed the archive of the CLOB matched the source. However when trying to export from the archive file it failed. AQT must be sensitive to something in the Optim Connect driver. DBVis can handle the export of a CLOB in the archive file.

    I'll continue to play with it but it doesn't look like there is a way around this issue. That being said this is still a super nice product for working with the different DBMS!

    ------------------------------
    Donna Simmons
    Application System Engineer
    ------------------------------



  • 12.  RE: Query Archives without DBVisualizer

    Posted Thu April 23, 2020 01:27 PM
    Donna, 

    What happens when you export the *LOB straight from the source, using the Optim Classic Client GUI? CLOB's can have all kinds of data elements in them that 100% cannot be interacted with using just any DB client. In many cases you need an application layer to present the data back to a user. I'm glad you found another tool in the AQT product but at the end of the day, you may or may not have an option here to interact with the CLOB from a DB client.

    ------------------------------
    Danny Lankford
    3M - IT Manager
    ------------------------------



  • 13.  RE: Query Archives without DBVisualizer

    Posted Thu April 23, 2020 05:34 PM
    ​Danny,

    Yes LOBs are a challenge. I confirmed the LOB in the archive is a direct match to my source. I browsed the archive and used the export LOB option to do the comparison. However that option works for only a single field. In a reporting scenario we want the ability to save LOBs store in an archive file to separate files. AQT can export LOBs beautifully from the DB source but throws the error when exporting LOBs from an archive file.  DBVis doesn't throw an error when exporting LOBs from an archive file. Thanks again for the recommendation. I do love the AQT GUI!

    ------------------------------
    Donna Simmons
    Application System Engineer
    ------------------------------