Batch Analysis

From SQLBWiki

Batch Analysis performs an analysis of one or several databases, and produces an HTML report which documents the entire model. To launch the batch analysis, select some databases in the object chooser (Use the usual Ctrl or Shift keys to select several databases), and right click "Batch Analyze". The HTML report produced is located in the output directory (set in the options) under the "sqlbhtml/<SERVERNAME>" directory.

An example of HTML output can be found here

The interest of the batch analysis is to run periodically as a scheduled task on a server. Doing so enables the developer community to point to the documentation page at any moment.

[edit] How to set up Batch Analysis as a Windows Scheduled Task

• create an argument file, say "arguments.xml", for example

 <arguments>
   <batch_analysis server="NGX3" databases="download apollo" crdate="31/01/1961" />
 </arguments>

• batch_analysis attributes:
  • server is the name of the server you need to perform the analysis for. The credentials used to connect will be the one used during the last interactive session on this server
  • databases is the list of white-space separated databases you want to scan
  • crdate is an optional object creation date. Only objects created after this date will be parsed. This is to allow shotrer batch anaysis.

• create a command file to launch SQLBrowser, containing for example:

 C:\temp\sqlbrowser\bin\sqlbrowser_w.exe -J-Xms64m -J-Xmx400m -J-DargumentsFileName="C:/temp/sqlbrowser/arguments.xml"

• open the Scheduled Task from the Configuration Panel and create a Windows Task using the command file
• schedule this task every day or every week, preferably when the database is idle. The analysis typically takes about 30 minutes for 10,000 objects
• important: SQLBrowser launched in batch mode will still use all the options and preferences which are defined for the user which is used to run the task. For instance, the last user and password used to connect to that server will be used.

Please see Command Line for information on the command line or the argument file syntax

Memory Considerations The Batch Analysis requires a good amount of memory to run correctly. The memory parameters are set by the -Xms64m -Xmx256m command line parameters. The maximum size depends on the number of objects scanned. Roughly, 100 megs are needed for 2000 objects. Important The java program should fit into physical memory. When batch analysis takes too long to complete:

1. verify that the -JXmx parameter is high enough given the number of objects have you scan.
2. verify that the java VM is not swapping. Check the 'page faults' column of the Windows Task Manager.
3. verify if the pause happens during a full gc. Turn gc loggin on (-verbose:gc) and see if the line '[Full GC' shows on the console when the program is paused
4. disable the 'Write Analysis' in the preferences, which is a very time and memory consumer task

[edit] Applications & Batch Analysis

The Column Write analysis shows each write to a permanent table. You can give an "Application" attribute, which will show as a specific Color in the HTML report, to tables or stored procs parameters. This color will propagate into the columns written to. To give "Application" attributes to tables or stored procs params, you need to create a couple of special tables with the following structure, in any database you like, as long as it this database belongs to the scanned databases:

 use mydb
 go
 drop table sqlbrowser_application_object
 go
 drop table sqlbrowser_application -- in this order!
 go
 create table sqlbrowser_application (
   application varchar(255) not null,
   color varchar(255) not null,
   primary key ( application )
 )
 go
 create table sqlbrowser_application_object (
   object varchar(255) not null,
   application varchar(255) not null references sqlbrowser_application( application ),
   primary key ( object )
 )
 go

The first table describes your 'applications' and attach a Color to them in Hex RGB format. For examples of color codes, see this link for instance.

The second table represents the association between objects (tables or procs) and applications. You fill this table with fully qualified object names (Note that the middle part has to be .. and not .dbo.) and with an attribute of your choice (generally, you can give an application name or a color) You will find these attributes appearing in the "Column Writes" page and in "schema.xml".

example:

 use mydb
 go
 insert sqlbrowser_application values ( 'Application_1', '0xFF4f4f' ) 
 insert sqlbrowser_application values ( 'Application_3', '0xFFF952' ) 
 ...
 go

or

 use sybsystemprocs
 go
 insert mydb.sqlbrowser_application_object 
   select lower(db_name())+'..'+lower(name), 'Application_1'
   from sysobjects where type in ('P','TR') and name like '%ua_%'
 go
 ...