Confirming CA Datacom SQL timestamp results with MS Excel

Products

• Datacom
• DATACOM - AD
• CIS
• COMMON SERVICES FOR Z/OS
• 90S SERVICES
• DATABASE MANAGEMENT SOLUTIONS FOR DB2 FOR Z/OS
• COMMON PRODUCT SERVICES COMPONENT
• Common Services
• CA ECOMETER SERVER COMPONENT FOC
• Easytrieve Report Generator for Common Services
• INFOCAI MAINTENANCE
• IPC
• UNICENTER JCLCHECK COMMON COMPONENT
• Mainframe VM Product Manager
• CHORUS SOFTWARE MANAGER
• CA ON DEMAND PORTAL
• CA Service Desk Manager - Unified Self Service
• PAM CLIENT FOR LINUX ON MAINFRAME
• MAINFRAME CONNECTOR FOR LINUX ON MAINFRAME
• GRAPHICAL MANAGEMENT INTERFACE
• WEB ADMINISTRATOR FOR TOP SECRET
• Xpertware

Issue/Introduction

CA Datacom SQL provides different ways to perform arithmetic on dates and times, but there is no singular function to provide a difference of two timestamp columns or values. Since there are multiple ways to determine this difference, is there a way to use a Microsoft Excel spreadsheet to validate the calculations found in my SQL query?

Environment

Release: DBCISF00200-15.1-Datacom-Database-Option for CICS Services
Component:

Resolution

Attached to this document is a Microsoft Excel spreadsheet called TimestampCalculationTest.xlsx. There is a procedure outlined to use this spreadsheet to validate the duration difference produced in the CA Datacom SQL query. There are several steps involved in using this:

1. After running the DBSQLPR query to produce a report of the two timestamp fields and the two Elapsed Time fields (if desired), save the output to a file and remove everything except the data records. Alternatively, you might want to enter or cut/paste your entries into this or another spreadsheet. If manually entering into this sheet, adjust the format of the cells if needed and copy the formula from columns H through M to your rows to see the results, otherwise, continue with step 2.
2. Transfer the file to the PC as a .txt file, and then open the file using Microsoft Excel. You may need to specify the date fields in their proper format (YMD, for example) and you may need to drop unneeded fields. When complete, your file should be open in Excel with 6 columns like these below.
3. Once your data is formatted like this example, you can copy the cells H7:M7 to the same columns on your opened spreadsheet. Paste these copied cells to cover the whole range of rows.
4. Copy cells J6:K6 to the top row of your spreadsheet, in the columns where the SQL Match Excel? results are found. These cells will count the number of times the SQL calculation did not match the Microsoft Excel calculation, so you can review them further. To find the mismatched cell(s), find "False" in the values for that column.

 

This spreadsheet uses formulas to calculate the duration between two dates in the format of number of days, hours, minutes and seconds and also years, months, days, hours, minutes and seconds. These formulas are based on the calculation algorithm concepts found in CA Datacom SQL, and take advantage of Microsoft Excel functions where possible. Two of the functions used - EOMONTH and EDATE - are part of the Analysis ToolPak add-in, which must be installed and loaded into Microsoft Excel 2003 and 2007, and are part of the base package for 2010 and above.

Additional Information

For more information about loading the Analysis ToolPak add-in, please visit the following links for your version of Microsoft Excel:
2003: http://office.microsoft.com/en-us/excel-help/load-the-analysis-toolpak-HP001127724.aspx
2007: http://office.microsoft.com/en-us/excel-help/load-the-analysis-toolpak-HP010021569.aspx
2010 and above: The date functions are already included

As always, please contact CA Technologies support for CA Datacom if you have further questions.