AIOps

 View Only
  • 1.  Structure of SQL (insert statement) generated by nco_osreport

    IBM TechXchange Speaker
    Posted Sat March 25, 2023 09:52 AM

    Posting on behalf of Ken Iida, as this was originally posted in the Instana forums.

    I used a Netcool/OMNIbus tool "nco_osreport" with -dbinit option, and got the SQL statements ( insert statements) to build New OMNIbus DB .

    Then , I noticed that the auto-generated insert statements has two patterns. See below.

      (1) insert into TableName (col1,col2,...,colN) values (val1,val2,...,valN)

      (2) insert into TableName values (val1,val2,...,valN)

    The difference between the two insert statements above is whether or not the column names are explicitly specified.

    I have two questions .

    (Q1) What caused this difference?

    (Q2) Can I control the differences in these output specifications using environment variables, registry values, etc.?

    Please teach me .



    ------------------------------
    Ken Iida
    ------------------------------



    ------------------------------
    Mike Mallo 
    Instana Technical Head of Product
    IBM Corporation
    mmallo@us.ibm.com
    ------------------------------


  • 2.  RE: Structure of SQL (insert statement) generated by nco_osreport

    Posted Sat March 25, 2023 10:34 AM

    There aren't any environment variables or flags that will help you.

    (NOTE: I didn't know any of this before reading this question - I just dug through and found this out.)

    The decision is made in the dbinit.xml file that is bundled inside the OSReport.jar file, and there is no documentation for this file. However, from looking at the contents of the file, the "insert into table values (..." syntax is used for table creation (it's in the "CREATE_TABLES" macro), whereas the "insert into table (col1...) values (..." is used in the "INSERT_TO_TABLES" macro. I just tested it out and found that changing the INSERT_TO_TABLES macro allowed me to change the behavor such that it always uses the "insert into table values (..." syntax.

    To do this:

    unzip OSReport.jar dbinit.xml

    edit dbinit.xml to change the INSERT_TO_TABLES macro from this:

    to this

    zip OSReport.jar dbinit.xml

    Then run osreport to see the difference.

    Frank



    ------------------------------
    Frank Tate
    Gulfsoft Consulting
    https://www.gulfsoft.com
    AIOps Experts. Contact us for implementation help.
    ------------------------------



  • 3.  RE: Structure of SQL (insert statement) generated by nco_osreport

    Posted Sun March 26, 2023 08:31 PM

    Thanks.

    According to the dbinit.xml description, Is the difference in the structure of the generated insert statement  due to the difference in the macros used (CREATE_TABLES macro or INSERT_TO_TABLES macro )?



    ------------------------------
    Ken Iida
    ------------------------------



  • 4.  RE: Structure of SQL (insert statement) generated by nco_osreport

    Posted Sun March 26, 2023 08:38 PM

    Yes. Exactly. 



    ------------------------------
    Frank Tate
    Gulfsoft Consulting
    https://www.gulfsoft.com
    AIOps Experts. Contact us for implementation help.
    ------------------------------



  • 5.  RE: Structure of SQL (insert statement) generated by nco_osreport

    Posted Sun March 26, 2023 09:49 PM

    Thanks. May I ask you one more question?

    Depending on the level of the 8.1.0 FixPack, only the pattern "insert into TableName values (val1,val2,...,valN)" seems to be defined. From which FixPack did Netcool/OMNIbus start using the two patterns ?



    ------------------------------
    Ken Iida
    ------------------------------



  • 6.  RE: Structure of SQL (insert statement) generated by nco_osreport

    Posted Mon March 27, 2023 04:07 AM

    The way I would go about finding that answer would be to start with OMNIBus 8.1, look at OSReport.jar, install FP1, look at OSReport.jar, etc.



    ------------------------------
    Frank Tate
    Gulfsoft Consulting
    https://www.gulfsoft.com
    AIOps Experts. Contact us for implementation help.
    ------------------------------



  • 7.  RE: Structure of SQL (insert statement) generated by nco_osreport

    Posted Mon March 27, 2023 07:45 PM

    OK. I understand. 



    ------------------------------
    --------------------------------------
    Ken Iida
    Ken.Iida@kyndryl.com
    NagoyaJapan
    --------------------------------------
    ------------------------------



  • 8.  RE: Structure of SQL (insert statement) generated by nco_osreport

    IBM TechXchange Speaker
    Posted Tue March 28, 2023 08:38 AM

    Thanks for the quick response Frank!



    ------------------------------
    Mike Mallo 
    Instana Technical Head of Product
    IBM Corporation
    mmallo@us.ibm.com
    ------------------------------