An End-User Perspective On Using NatQuery To Extract Data From ADABAS. Presented by Treehouse Software, Inc.
An End-User Perspective
To Extract Data From ADABAS
Presented by Treehouse Software, Inc.
NatQuery is a workstation-based tool that provides for a Graphical User Interface (GUI) that allows an End-User to easily extract data from a remote ADABAS / Natural Server, and then integrate this data into common workstation tools. Shown above is the NatQuery “desktop”.
For the purpose of this walkthrough, let’s assume that a user wants to extract some Employee data along with Vehicle data that may be related to the given employee. Specifically, the user wants the extracted data to be put in Excel, and they want to select employees who have a last name of “SMITH” and who live in either “BREMEN”, DERBY” or “BARCELONA”.
To begin the process of creating this query extract, a user would click on the New Query icon. This will result in the presentation of a Select File window, as seen on the next slide.
When first displayed for a query, the Select File window presents all files that have been made available for extraction by a NatQuery Administrator. These files can either be ADABAS files or can alternatively be any sequential files on the Server.
To select the Employees file for data extraction, the user would click on the Employees file name, and would then click on the Select button. This action would invoke the Select Fields from Employees file window, as seen on the next slide.
The Select Fields from Employees window displays two “panes”; the left “pane” will list all fields that are available for selection and the right “pane” will show fields already selected. Through the Select Fields from Employees window, the user will indicate which fields that they wish to have extracted from ADABAS.
To select any given field for extraction, the user can simply double-click the field of interest in the left pane, and NatQuery will add this field into the right “pane”. To assist the user in locating a specific field of interest, the user may invoke a search function to quickly locate any given field of interest.
If the user has a question on what any given field is or represents, they can right-click on the field of interest in the Select Fields from Employees window. This action would invoke a window that details all field-level information that was available on the given field (as originally stored in Predict – a Data Dictionary product common in Software AG environments).
As the user selects the fields they wish to extract, these fields will be displayed in the right “pane” of the Select Fields from Employees window. Once all of the required fields are selected, the user would then click the OK button, this will invoke the Select Files window, as seen on the next slide.
In displaying the Select File window for the second time, the user can incorporate fields from other files into the extract. At this point, NatQuery has automatically reduced the list of available files to only those that can be logically accessed from the Employees file – the user does not need to know how this link will occur.
To select fields from the Vehicles file for extraction, the user would continue the field selection process by clicking on the Vehicles file name, and would then click the Select button. These actions will invoke the Select Fields from Vehicles window as seen on the next slide.
With the Select Fields from Vehicles window displayed, the user would then select the fields that they would like extracted from the Vehicles file. Once all fields of interest have been selected, the user would then click the OK button. NatQuery will respond to this by invoking the Selection Logic window, as seen on the next slide.
The Selection Logic window allows the user to specify the selection criteria which will be applied to the records that will be extracted. Since our current example has two files having been selected for extraction, the user would first indicate the file against which a Selection logic statement will apply.
The user can now enter the first selection logic statement for this query, which was to select employees who have a last name of “SMITH”. This is done by clicking the “NAME” field, clicking the “Equals” operator, then clicking the Constant button; this will invoke a window entitled Define Constant, as seen on the next screen.
With the Define Constant window displayed, the user can now enter the value of “SMITH”. Once entered the user would then click the OK button to return to the Selection Logic window, as seen on the next screen.
Having supplied the query with “SMITH” the user would then click the AND button to add the rest of the needed selection logic, this will add “AND” into the Current Selection Logic window. At this point the user is ready for the next select logic statement.
The user now needs to build the second Selection Logic statement. The user would accomplish this by first selecting the Employee file, then selecting the CITY field, then clicking on the Equals operator, and then clicking the List button. This would invoke the Define List Variable window to allow the entry of OR logic; “DERBY”, or BREMEN”, or “BARCELONA”.
The User has now built a query that reads – “Find all Employees named SMITH who live in DERBY, BREMEN, or BARCELONA.” Since this completes the Selection Logic that was to be applied, the user can now click the OK button to close the Selection Logic window.
The query is now ready to “Send to Server.” In most cases this is an automated FTP over TCP/IP process that puts the request into Batch on the Server.
Clicking the “Send to Server” icon invokes the Query Description window. Here the user can name the query, add a short description, and a longer description to aid in referencing the query at a later date.
The Send To Server Options window allows the user to designate the “target” (Extract Type) for the data to be extracted, and in our example the user would select Download into Excel. The user may then click the OK button.
The default Database Field Names will be used as Column Headers in the Excel “target”. However, the user may edit these as they wish through the “Edit” tab on this screen. When the user is satisfied with the column names, the user would click the OK button.
With the column headers specified, the query is now ready to be sent to the remote server, and the user is prompted for confirmation to do this.
Here the user is being alerted that their query has been sent to the server for execution, and are provided with information on this specific query extract.
Clicking the Check Server icon will result in the display of the Check Server window, which provides information on extraction processes that have been previously Sent, as seen on the next slide.
Here we see the Check Server window, with this window initially displaying information about any Query extracts that have been sent to the server. This display currently shows the query just submitted as having a status of “PENDING”. To see if this status has changed, the user would click the Check Server for Update button.
Clicking the Check Server for Update button will cause the display to change if any requests that were previously sent have been processed. Here we see that the request which previously was showing as “PENDING” now shows as “DONE”, with additional information being displayed concerning the extracted data itself.
With a request showing a status of “DONE”, the user may now proceed to bring the extracted data down to their workstation. This is accomplished by clicking anywhere on the text of the request (this will cause the entire request to be come highlighted), and then clicking the Retrieve Request Output button.
Subsequent to clicking the Retrieve Request Output button, NatQuery will prompt the user for a directory and file name into which the extracted data should be downloaded. A default “Output” directory and file name is automatically provided, which the user can change if they desire. Usually, the user only needs to click the Save button to initiate the download.
With the initial download of data completed, the user is now being reminded that their original intent was to download data into Excel. At this time the user may click the YES button to go ahead an invoke Excel, or they can click NO to continue with other tasks.
By Clicking the YES button, NatQuery will automatically invoke Excel where the user will be presented with the requested data, along with the desired column headers.
With the data downloaded and placed into Excel, NatQuery returns to the desktop where the basic elements of the query just processed are displayed. This query can now be saved for future use by this user or other users. The query can be closed by clicking on the Close Query icon.
The Viewer should be aware that this is an Administrator’s Desk top and that certain menu items will not be available to an End user.
Through this walkthrough, we have attempted to show a brief overview of the power of NatQuery and it’s ability to access and retrieve ADABAS data. There is much more functionality then can be shown here, we encourage you to open a dialog with your solutions provider to begin a Trial at your site.
URL: www.treehouse.comE-Mail: [email protected] Phone: (412) 741-1677