Videos

This video is XYGATE Access Control (XAC) – Keystroke Logging. The objective of this video is to show how XAC can be configured to provide full and partial keystroke auditing.

This video will focus on the Pathway management menu. This menu will help you check the status of your XMA installation, and start and stop the Pathway.

This short introduction to the XYGATE software standard Windows GUI clients will give you a little background information about the how to find the installation software, downloading the software from the XYGATE installation subvolume, and executing the Windows installation programs.

SQLXPress stores query execution plan information in its own database rather than regenerating plans dynamically for every function, which allows users to perform a number of useful tasks when optimizing query execution.  This video shows how to use SQLXPress to quickly analyze, tune and optimize NonStop SQL queries.

Learn more about SQLXPress
https://www.xypro.com/merlon/

SQLXPress stores execution plan information in its own database rather than regenerating plans dynamically for every function, which allows us to perform a number of useful tasks when optimizing query execution. Select Work with Execution Plans to access this database.

The first thing we can do to is to report on execution plans that display particular attributes of queries that we are interested in. Select Query Execution Plans to generate these reports. The first page limits the objects we are looking for, in this case select standalone queries, which are queries that have been manually entered or scanned from EMS tracing of ODBC and JDBC activity.

The second page specifies the attributes we are looking for. There are several properties to choose from, but for our example we will look for queries with high statement cost.

Press Continue to go to the next step and execute the query. From the list of entries, we can select a query of interest and display the execution plan for our review.

SQLXPress makes the plan more readable by displaying it as a tree. Each plan fragment is colour-coded, displaying the type of operator, the percentage of the plan spent within that operator and, where appropriate, the name of the object to which the operator applies. Above each node is the number of rows expected to be returned from that node. The plan percentage is also displayed as a red bar to the right of the node to highlight points of high percentage. In this case, we can see that 73% of the plan is spent on a file scan of X21DET. Also, hovering over a node will display the details of that node.

Select the Details tab for a text view of the plan, which can be navigated by selecting nodes in the tree to the right.

Select the Summary tab for a grid display of the plan operators as well as the most important attributes of those operators.

The SQL Statement tab displays the text of the statement for the execution plan.

A more comprehensive way to find poorly performing queries is via the Query Advisor. Select Use the Query Advisor from the SQL/MX Execution Plans page. Similarly to the Query Execution Plans page, you can choose to work with programs for static SQL, SQLXPress queries, or MFC locations for this analysis.

Press Continue to choose which queries we are interested in and enter _QR* to select the same query we had found.

Press Continue to select the advice thresholds. These are “rules of thumb” used to generate recommendations. For instance, the default of 60 days indicates that statistics that have not been updated in the past 60 days will trigger a request to update those statistics. Press Fetch Queries to extract the query we are interested in, then Generate to run the Query Advisor.

Click Continue to view the analysis results. This will recommend environment changes to improve the query’s performance, such as adding indexes or updating statistics. The advisor will also suggest syntax changes, such as optimization of LIKE predicates using substrings, that may improve the performance of the query.

The next step is to tune our problem query by selecting Use the Query Tuner from the SQL/MX Execution Plans page. The Query Tuner is a prototyping tool that allows us to perform in depth analysis of the effects of changing the actual query rather than adding statistics or indexes.

First, we open the query we are interested in, or create a new query by entering text in the Query Text panel. Queries are stored in multiple snapshots, each of which represents one execution plan generated. This allows us to easily compare the effects of changes made to our query.

The Execution Plan tab displays the execution plan in the same way as we saw in the SQLXPress client.

The Execution Statistics tab displays how the query performs in terms that are more easily discussed than query cost. This tab shows the compile and execution time of the query by pressing the Run Query button, which executes the query but does not return results to the client. You can run the query multiple times to avoid “cache bias”, which results in uneven response times based on the data retrieved from the cache, rather than from disk files.

Lastly, the Performance charts tab lists the query’s performance between snapshots. In this case, we can see that using Control Query Defaults to use nested joins only reduced the execution time by a factor of ten.

SQL syntax can be complex, so SQLXPress includes a visual query builder to help in creating new queries.

Learn more about SQLXPress
https://www.xypro.com/merlon/

SQL syntax can be complex, so SQLXPress includes a visual query builder to help in creating new queries. Select Build a Query from the SQL/MX menu.

Press the Add Table button to add a table or view to the query. For each table, press the Add button to include that table in the query. Once you’ve added the tables you want, select Auto join to set the initial join conditions between tables and close the Add Table form. Auto join matches key columns in each table with columns of the same name and type in other tables.

To modify or delete a join, select the line joining the tables, then make any desired changes in the Joins tab, or press the delete button to remove the join. To add new joins, just drag a column from one table to another.

Beside the Builder Tab, you will see an SQL Tab, which displays the SQL text of the query. You can change the text her and it will be reflected in the Query builder tab, so I can specify an individual column, then switch back to the Query builder tab to see the reflected change.

Detailed changes can be made using the tabs at the bottom of the query builder. For instance, select the Where tab to filter this query, then press the plus sign to add criteria. To the right of the criteria expression is a helper button that will open an expression builder form. Here we can choose columns or functions, in this case we will just choose to display the orders for a single customer.

We can now select the Parameters tab to provide a default value and description of the parameter selected and we can now save and execute the query.

On the top left corner of the Query Builder is the Query menu, which allows us to save this query in the SQLXPress database. Press Save and save this query to a unique name. It is now stored on the server and may be used to retrieve data through the SQLXPress client or the XPressView client.

Press the Run button to execute the query, which will open the SQLXPress data browser with the query results. Each time the query is opened, you will be prompted for the parameter values.

Although SQLXPress does include wizards to help create database objects, most databases grow from existing ones. There are several features of SQLXPress that will assist in this process, but the most commonly used one is the Show DDL wizard.

Learn more about SQLXPress
https://www.xypro.com/merlon/

Although SQLXPress does include wizards to help create database objects, most databases grow from existing ones. One example is creating a QA environment from a production database.

There are several features of SQLXPress that will assist in this process, but the most commonly used one is the Show DDL wizard. From the SQLXPress menu, select Manage the database, then Show DDL.

The first page of the wizard allows you to select the object you are interested in. You can select multiple items by wild card by checking Enable Wildcards. This is explicit because SQL/MX names can include the percent and underscore wildcards used in SQL, particularly underscores. If Enable Wildcards is not checked, SQLXPress will select the individual name that matches the pattern entered.

Press Add to add items to the list, then remove any you don’t wish to keep.

Press Continue to go to the next step. This step defines the ancillary objects to include and how the DDL statements will be formatted. For instance, check Include Location to preserve the disks on which partitions will be placed, then choose whether the system names or full file names will be included. Please note that choosing to retain these names will not allow you to create objects on the same system.

Press Continue to go to the next step. This page allows you to make large-scale alterations to the DDL script that will be created. You can create objects on a different schema, scale their sizes, and specify new locations here.

Press Continue to go to the next step, then enter the mapping between the selected and target schemas.

Press Continue to go to the next step, then choose the scaling factor. This is especially useful when creating copies of large environments, such as production databases, to smaller test environments.

Press Continue to go to the next step, then enter the disk volumes to be used for object locations.

Press Continue to go to the next step. The last page allows you to generate the script, either as MXCI commands, or SQLXPress’ MSCRIPT input. Press the Execute button to create the SQL objects as entered, or save the script to a text file to be altered and executed later.

Discover scans your database every day to find and predict issues with files and disk volumes, performing automated maintenance or letting you know that something needs to be done.

Learn more about Discover
https://www.xypro.com/merlon/

Discover scans your database every day to find and predict issues with files and disk volumes, performing automated maintenance or letting you know that something needs to be done.

Identifying issues is done using conditions, for which there is a large list of predefined conditions that are commonly looked for, as well as user defined conditions that may be a combination of predefined conditions as well as attributes of a file or disk volume. In this case, we will be looking at file full conditions. These appear in the conditions list as FILEFULLn, where n is a number between one and four. These numbers identify projection intervals, over which Discover estimates growth, and may appear for file full, disk full, or allocation error projections.

Looking at the Monitor attributes, we see that the projection intervals are set from lowest to highest, or most to least critical. In this case, the intervals are set to the default 1, 7, 30, and 90 days. This means that the FILEFULL1 condition will be triggered if Discover estimates that the file will be full within one day and FILEFULL4 for files that may be full in the next three months.

In this case, FILEFULL1 generates a critical message to EMS and sends an email message indicating the problem to the appropriate person.

First, we can look the file full report, which lists the files that will be full within one of the projection intervals and see our sample file, $D1.KWBIG.CLOSEBIL. Navigating to this in the Files tree, we can see that file’s growth in chart format. We can now start the Discover scan, which normally runs automatically once a day, to see how this file will be handled.

Our sample file will be full within about a week’s time, so we are interested in the FILEFULL2 condition. This performs up to three actions; an EMS message is generated using ALERT to the main console and the maximum size of the file is extended sufficiently to accommodate another week of growth. If Discover could not increase the file’s MAXEXTENTS, either because of an error or because the maximum has been reached, the IFFAIL token indicates that an email message will be sent to the appropriate person indicating that Discover’s palliative action has failed.

So, we now move to the TODO list, where we can see that the file has been extended.

Reloads can be resource intensive, especially with large files, and just reloading with a low rate may reduce system impacts, but may also require excessive completion times. MARS deals with this in two ways: specifying only the files that need to be reloaded and performing those reloads in a controlled fashion.

Learn more about MARS
https://www.xypro.com/merlon/

Reloads can be resource intensive, especially with large files, and just reloading with a low rate may reduce system impacts, but may also require excessive completion times. MARS deals with this in two ways: specifying only the files that need to be reloaded and performing those reloads in a controlled fashion.

Rather than reading all the data in a file, MARS takes a sample to minimize the overhead of determining which files need to be reloaded. Select the Evaluate Files Report from the Tools menu to evaluate the reload state of your database in real time.

Select Criteria, then Set to choose the files to evaluate. Both Guardian and SQL/MX patterns may be chosen, as well as other criteria to identify the files to be scanned. Under the Reload Triggers tab, select the thresholds for the file attributes for which you would consider a reload would be required for the files in question, then press OK to display the list of files.

Note that reloads are performed at a partition level, so you will see the evaluation results by Guardian location, although you can choose to display the SQL/MX object name, if desired. Note that the “Will Trigger Reload” column indicates that two of the selected files would be reloaded according to the reload triggers we entered.

Once you are happy with the list displayed, select Criteria, Create Fileset from Criteria from Fileset to create a fileset using this report as a starting point. Give the fileset a name, then choose how and when the files will be scanned, as well as a reload schedule under which the resulting reloads will be performed.

Then, press the home button, then Manage Reload Fileset Scans. Beside the new fileset, you will see that it is scheduled to scan for the first time tonight, but we can manually start the scan via the Actions menu. We can then open the Manage Reload Queue to see the queued reloads in a waiting state.

Those reload will not be performed, however, until the DEFAULT reload schedule opens. The Manage Online Reload Servers page shows the next time that reload schedule will be open, at 1:00 am tomorrow morning. If we look at the properties for that reload schedule, we can see that it is open on weekdays from 1 to 7 am and will run up to three reloads at the same time on CPUs 0 and 1.

For expert users, who are intimately familiar with SQL syntax, client interfaces can be cumbersome, so SQLXPress provides a “whiteboard” from which SQL statements can be executed directly.

Learn more about SQLXPress
https://www.xypro.com/merlon/


For expert users, who are intimately familiar with SQL syntax, client interfaces can be cumbersome, so SQLXPress provides a “whiteboard” from which SQL statements can be executed directly. This may be started from the Tools menu or by clicking Execute an SQL Statement on the SQL/MX or SQL/MP main menus.

You can enter one or more SQL statements in the SQL statements panel, with each statement terminated with a semicolon. Auto completion makes it easy to specify columns without syntax errors. Select Execute Line to execute the line on which the cursor resides, or Execute to execute all the statements in the panel.

When statements are executed, the results appear in the three tabs below the statement panel. The Messages tab lists error and status information about the query, the Query Results tab lists a grid view of SELECT statement results, and the Statistics tab lists the SQL statistics for executed queries. The results and statistics tabs may be copied and exported from the toolbar options.

Some of the useful features on this page include the SQL select, insert, update and delete templates, which display the appropriate statement for the indicated schema and table name. These statements can then be modified as desired.

On the left hand side, a history of your entered statements appears that can be moved into the statements panel to be executed again or altered to create new statements.

Beside the history tab, you can create snippets of SQL structures that you use often that may then be added to the statement panel to help build your statements. To create a snippet, enter the desired text in the statements panel, then press Add snippet.