12 Replies Latest reply on May 15, 2020 12:29 PM by carol goldberg

    Transposing data from a BMC Mainview View

    carol goldberg
      Share This:

      I am wanting to take a view example below and transpose the data to make it loadable into a DB2 table. Coumns would be Request , inserts found in Cache etc. Is there a way to do this within Mainview , I am not opposed to making my own view. Aslo How do I make a new view sharable? I had been using the unloading of data , but I want it to match the data values as you see them in the view.

      I am looking at options such as SAS Proc transpose, or Syncsort but that is just adding to the level of complexity.

       

       

      Thanks in Advance,

      Carol

       

       

                      ,"DSNP DYNAMIC STATEMENT CACHE AT 8 AM DAILY ",                 

      "CMD> CON DSNP;TIME * 8:00;STCACHED "                                           

                                                                 REPORT PAGE   1,     

      06MAY2020  08:22:26           MainView Batch Report               PAGE   1,     

      STCACHED,        ,DSNP    ,*       ,06MAY2020, 08:00:05   ,MVDB2   ,D    1,     

                                                                                      

      --Dynamic SQL Cache Details          ,    06MAY,,    08:00,                     

                                           ,         ,,         ,                     

      . SQL Cache Statement Analysis       ,         ,,         ,                     

                                           ,         ,,         ,                     

                                           ,         ,,         ,                     

      --SQL Cache in Statement Pool        ,         ,,         ,                     

      Total Pages                         ,         ,,        0,                     

      Pages Used                          ,     0.00,,     1240,                     

      Free Pages                          ,     0.00,,        0,                     

      # of Statements in Pool             ,         ,,      178,                     

                                           ,         ,,         ,                     

      --Global Cache Usage                 , Interval,,  Session,                     

      Requests                            ,    89229,,233957804,                     

      Inserts                             ,    23456,, 41078142,                     

      Found in Cache(Short Prepare)       ,    27499,,165416116,                     

      Not Found in Cache(Long Prepare)    ,    61730,, 68541501,                     

        • 1. Re: Transposing data from a BMC Mainview View
          Kevin McGuinn

          HI Carol,

           

          I am the VP of customer success for ZSO.  I'll get a field tech to look at this and reply back soon. 

           

          Thank you for your question and activity on BMC Communities!

          1 of 1 people found this helpful
          • 2. Re: Transposing data from a BMC Mainview View
            Sam Avery

            Hi Carol,

             

            One method that might achieve what you're after is using a combination of MainView Batch and view customization (if desired).

            This method results in the view data being exported in a CSV format, which can be kept on the spool or saved to a dataset and then used as input to REXX/SASS to build the DB2 load parms.

             

            1. Start by deciding which view you would like to export data from, I will use view STCACHED as an example.
              If you wish to customize the view, on the view's COMMAND line, type CUST and press Enter.
              From here, exclude the fields you would like to omit using the point and shoot feature with command X.
            2. Once the view is setup as you wish, press END (PF3) to save, giving it a different name i.e. - STCACHEC. Note - this will be saved in your personal userid.BBVDEF dataset. If you would like it to be accessible by all users, you can place it in your site.SBBVDEF dataset. (for reference, these are allocated via the MainView CLIST).
            3. In order to generate the view data in CSV format, first access the MainView Selection Menu

              1. Select U - Utilities, Tools, and Messages
              2. Select 2 - MVBATCH
              3. Select 0 - Setup, enter details similar to below depending on your naming standards and save for future use.
                mvbatch1.png
              4. Select 1 - Generate and populate values similar to below
                mvbatch2.png
              5. Press END (PF3) to backout (note - you will be prompted to allocate the output PDS dataset if it doesn't exist), and you will then be presented with the JCL to submit. A copy of the job will be saved in the library specified in step 3 for future reference.

             

            Hope this helps

            1 of 1 people found this helpful
            • 3. Re: Transposing data from a BMC Mainview View
              Gilles Robert

              Hello Carol,

               

              To complement what Sam I wanted to add some dateails to his answers as well as pointing out another method of extracting information from MainView in order to process with some other mechanism. In your case load it into Db2.

               

              1- MainView in batch

              As Sam indicated you can use MainView in batch in order to output the content of a View in CSV format (asis is also possible). The issue I understand you are facing is STCACHED is a detail View and possibly the tabular View STCACHE may not have the fields you want.

              If you issue the CUSTOM command while on the STCACHE View and enter E to show excluded field you may check if all the field are there. If so it is a simple matter to include them and save that customization. If the fields showing in STCACHED are not showing at all in STCACHE I suggest you contact your BMC Software Consultant to get some help.

              The customize view will be save in your userid.BBVDEF. Typically it is allocated concatenated in front of a site SBBVDEF. You can copy your customized view to your site SBBVDEF to share it with our colleagues.

              STCACHE have history, you could run one batch job per day and set a TIME to get one full day at a time; using a relative format for the date so it can run daily.  For example TIME *-1 00:00 24H will get in one fell swoop all intervals for yesterday ending at midnight. You can try this command online.

              The issue with this method is you have to customize the View as you are not insulated from change to the underlying View.

              STCACHE.jpg

              2-)  Use our Data Mapping and Extraction utility.

              We provide records layout for history based on the MainView Infrastructure. See the Technical bulletin found at https://documents.bmc.com/supportu/documents/12/43/521243/521243.pdf for details.

              In particular the data maps BBPUS100  MVDB2 Stats Record (associated to the MVDB2 Status views that are using DB2 Statistical data) .

              This support was recently added, you may need to check that you have these PTFs applied: BPD4904 BQY2254.

               

              Hope this help, feel free to contact me if you need some help as I'm a member of the BMC's Customer Success organization.

               

              Regards, Gilles

              2 of 2 people found this helpful
              • 4. Re: Transposing data from a BMC Mainview View
                Kevin McGuinn

                Thank you Sam & Gilles!

                • 5. Re: Transposing data from a BMC Mainview View
                  carol goldberg

                  I using  MVBATCH to write out the views as data files to load the report data to a version of a db2 table.

                   

                  I just need the view to load into my own version so it is loadable into db2 table that is inline with the view.

                   

                  I had a ticket into support to load the DB2 tables from the Archive  files. I still haven't gotten that resolved completely, the issue is  it is not populating all the DB2 tables. I am using DOMRPT to dynamically allocate the archive files. Which is not really document very well. I am using from the samplib the SMF job streams, and the control cards that need ot be used to populate what table is not well documented, and support was not able to map the reports to what DB2 tables get loaded.

                   

                  I do have an open ticket out there.

                   

                  At this point instead of processing all that data;  I was going to see if I can use MVSBATCH to replicate various views,  and load that data into my version of a DB2 table. That way i dont have to process alot of data, plus it wont match the views because the values are calculated in the view.

                  I figured it would be less processing time.

                  • 6. Re: Transposing data from a BMC Mainview View
                    Richard Solomon

                    Hi Carol, just checking in were you able to get the data from the view into a Db2 table  ?

                    • 7. Re: Transposing data from a BMC Mainview View
                      carol goldberg

                      Thank you for checking. I have been trying to see if I can use the view as is and load it into a table. For Load plus do your character fields have to have a delimiter?

                       

                      The first view is the bufferpool view, the issue is there is no delimiter around the character columns.

                       

                      I am having issues with loading the data into a db2 table in Format CSV format. I digress, this should not be this difficult.

                      I would like to keep the view as is, because when maintenance happens it may impact my output exported  file.

                       

                       

                      Regards

                       

                       

                       

                      Carol Goldberg

                      Texas Comptroller of Public Accounts

                      Software Development

                      512-475-0612

                      Carol.Goldberg@cpa.texas.gov<mailto:Carol.Goldberg@cpa.texas.gov>

                      111 E 17th Street

                      Austin, Texas 78774

                      • 8. Re: Transposing data from a BMC Mainview View
                        Richard Solomon

                        Hi Carol,

                        Just heard from our support team ..... more coming soon.

                        • 9. Re: Transposing data from a BMC Mainview View
                          carol goldberg

                          Is it possible to put in a request that they would make the CSV loadable using BMC PLUS?

                          When you do an export using CSV, it does not enclosed alphanumeric columns that are needed when using BMCPLUS Load.

                          We were testing out NGT but there were to many issues, so that is why I am suggesting to make it usable by BMC Plus Load.

                           

                          Just a thought;

                           

                          Carol

                          • 10. Re: Transposing data from a BMC Mainview View
                            Mike Donaldson

                            Hi Carol.  I'm the Director of Customer Success for DB2.  I'm going to have one of my TSCs reach out to you on this topic and see if we can't help come up with a solution for you.

                            • 11. Re: Transposing data from a BMC Mainview View
                              carol goldberg

                              The best number to reach me at is

                               

                              678 361 8713

                              • 12. Re: Transposing data from a BMC Mainview View
                                carol goldberg

                                My best number is  678 361 8713,  now that we are shelter in place.

                                 

                                 

                                Thanks, Carol

                                 

                                 

                                Carol Goldberg

                                Texas Comptroller of Public Accounts

                                Software Development

                                512-475-0612

                                Carol.Goldberg@cpa.texas.gov<mailto:Carol.Goldberg@cpa.texas.gov>

                                111 E 17th Street

                                Austin, Texas 78774