UIC Data Conversion and Submission via CDX Node Client UIC Database V2 6/16/2010

1. UIC Data Conversion and Submission via CDX Node ClientUIC Database V26/16/2010

2. XML Conversion Tool in the CDX Node Client • Can be download at : http://test.epacdxnode.net/cdx_client/ • Allows conversion from an arbitrary source of data to an arbitrary data format • UIC: conversion from a data source backed by a relational database to the schema-defined XML format • Can be used to convert from three source database platforms: • MS Access (database) • Oracle (database/view) • SQL Server (database/view)

3. Assumptions • Prior knowledge of ODBC and JDBC • Source database is accessible via ODBC or JDBC • Source database schema MUST adhere exactly to the standard UIC source database format (UIC MS-Access Database): • Standard table and column names • Standard data types for the columns • Configuration needs to complete before using the XML conversion tool

4. MS ACCESS Configuration

5. Configuration (MS Access) • If the default UIC MS Access ODBC data source named ‘uic’ is used and it was previously configured on the system, no further configuration required • If using a previously defined ODBC data source, the following actions need to be performed: • Locate the data source configuration file in cdxclient/conf/uic directory on the Desktop (Windows) or in the home directory (UNIX)

6. Configuration (MS Access) (cont.)

7. Configuration (MS Access) (cont.) • open the uic-ds.xml file in your favorite text (or XML) editor

8. Configuration (MS Access) (cont.) • Locate the section <property name="url"> <value>jdbc:odbc:uic</value> </property> • Change the string ‘uic’ to the name of your previously defined ODBC data source • Save the file

9. Configuration (MS Access) (cont.) • If no ODBC data source was previously defined, create it using the Windows ODBC Utility: • Open “Administrative Tools” in the Control Panel • Start the ‘ODBC Data source Administrator’ by clicking on ‘Data Sources (ODBC)’ icon

10. Configuration (MS Access) (cont.)

11. Configuration (MS Access) (cont.) Click on the ‘Add…’ button

12. Configuration (MS Access)(cont.) • Select ‘Microsoft Access Driver’ and press the ‘Finish’ button

13. Configuration (MS Access) (cont.) • Fill in the Data Source Name • uic • Fill in the Data Source Description • Press the ‘Select…’ button and select the UIC MS Access database (*.mdb file) • Press ‘OK’ button on ‘Select Database’ screen

14. Configuration (MS Access)(cont.) • Press the ‘Advanced…’ button

15. Configuration (MS Access)(cont.) • The ‘Set Advanced Options’ screen will pop up

16. Configuration (MS Access)(cont.) • Fill in ‘Login name’ • uic • Fill in ‘Password’ • uic • If using different login name and password the respective values will have to be changed in uic-ds.xml file • Press ‘OK’ on all the ODBC related open screens

17. ORACLE Configuration

18. Configuration (Oracle) • Open uic-ds.xml file (see slides above) • Modify the following section <property name="driverClassName"> <value>sun.jdbc.odbc.JdbcOdbcDriver</value> </property> to be: <property name="driverClassName"> <value>oracle.jdbc.driver.OracleDriver</value> </property>

19. Configuration (Oracle) (cont.) • Modify the following section <property name="url"> <value>jdbc:odbc:uic</value> </property> to be: <property name="url"> <value> jdbc:oracle:thin:@<your host>:1521:<your sid> </value> </property> where <your host> is your oracle database host, and <your sid> is your oracle SID

20. Configuration (Oracle) (cont.) • Modify the following sections <property name="username"> <value>uic</value> </property> <property name="password"> <value>uic</value> </property> to contain your Oracle database user id and password in respective <value/> elements (in place of ‘uic’) • Save the file

21. MS SQL SERVER Configuration

22. Configuration (MS SQL Server) (cont.) • Open uic-ds.xml file (see slides above) • Modify the following section <property name="driverClassName"> <value>sun.jdbc.odbc.JdbcOdbcDriver</value> </property> to be: <property name="driverClassName"> <value>net.sourceforge.jtds.jdbc.Driver</value> </property>

23. Configuration (MS SQL Server) (cont.) • Modify the following section <property name="url"> <value>jdbc:odbc:uic</value> </property> to be: <property name="url"> <value> jdbc:jtds:sqlserver://<your host>:<port>/<db name> </value> </property> where • <your host> is your SQL Server database host • <port> is the port on which your SQL server is listening for connections • <db name> is name of your UIC database

24. Configuration (MS SQL Server) (cont.) • Modify the following sections <property name="username"> <value>uic</value> </property> <property name="password"> <value>uic</value> </property> to contain your MS SQL Server database user id and password in respective <value/> elements (in place of ‘uic’) • Save the file

25. How to Use Node Client (Client)

26. Running the Client • If the CDX Node client has been installed previously, run it from the icon on Desktop • If the CDX node Client has not been installed, open the following URL in the browser: • https://test.epacdxnode.net/cdx_client • Click the ‘CDX Client’ link on the page

27. Running the Client (cont.)

28. Running the Client (cont.) • The ‘Java Starting’ screen be displayed • The client may automatically update itself from the network • After a few moments, the client login screen will be displayed

29. Running the Client (cont.) • Type in your NAAS user id and password • Press the ‘Login’ button

30. Running the Client (cont.) • Upon successful login, the client’s main screen will be displayed

31. Converting and Submitting data • Select the ‘Submit via Conversion Service’ menu item from the ‘File’ menu

32. Converting and Submitting Data (cont.) • The ‘Submit via Conversion Service’ screen will be displayed

33. Converting and Submitting Data(cont.) • Enter a description for the submission • Select the CDX Node to which the submission will go (CDX Dev, CDX Test, CDX Prod) • For the testing period we will use CDX Test • Enter the conversion parameters (these values will go into XML header for your submission- see the UIC FCD for required Header Elements) • Select Operation: • ‘Delete – Insert’ for actual submission • ‘presubmission’ for validation only submission • Press ‘Preview Conversion’ to see the resulting XML without submitting • Press ‘Submit via Conversion Service’ to submit to the selected node upon conversion

34. Converting and Submitting Data(cont.) • If doing a ‘Preview’, upon successful conversion, a ZIP file will open with XML file in it • Note: each conversion result is given a unique identifier reflected in the resulting file name. You may want to change the name prior to submittal

35. Converting and Submitting data(cont.) • If doing ‘Submit via Conversion Service’ ’, upon successful conversion, the resulting ZIP file will be submitted to the selected node • The client will present details of submission in the bottom panel of the screen • Internal transaction id – maintained by the client • External transaction id – transaction received from submit from the target CDX Node • Status of transaction on the target CDX node after submittal

36. Converting and Submitting data(cont.)

37. After Submission – check transaction status • Press the ‘Check Transaction Status’ button to update status of the transaction from CDX Node

38. After Submission (cont.) • For regular processing, Status will change to ‘FAILED’ or ‘COMPLETED’ when transaction processing is done • For presubmission, status will change to COMPLETED regardless of validation status.

39. After Submission – check transaction status (cont.) • Click the internal transaction id to see the transaction details • A list of documents and reports will be displayed including: • ZIP file submitted under this transaction • Any files downloaded so far form CDX node related to this transaction

40. Transaction Details

41. Documents Under Transaction • On the transaction detail screen, press the ‘Document Download’ button (next to ‘Check Status’) to see the list of documents associated with the Transaction • The screen with list of documents will be displayed

42. Documents Under Transaction (cont.) • For both regular and ‘presubmission’, there could be the following documents for download (standard files): • Original submission file (a zipped xml file; original schema v1 for schema v2 transformation) • Transformed to v2 schema xml file • Validation Results – row validation results (of schema/schematron validation of original submission) • detail_report_<date>.xls– row validation results in Excel format • summary_report_<date>.xls– validation results with extra information, in Excel format

43. Documents Under Transaction (cont.) For regular submission, if: • PASSED (COMPLETED) validation: data was submitted to back-end; there would be additional files: • BackEndDistribution_*.zip – actual file that is submitted to back-end • Notify_<original file name>.xml – notification that is sent from the back-end, indicates status of processing on back-end • <original file name>_report.zip – contains results of processing on the back-end • If transformation happened to ‘blank’ allowable warnings, there also will be a file containing transformed data • FAILED validation: data was NOT submitted to back-end; there are no additional files listed above, just standard files show up

44. Documents Under Transaction (cont.) List of file for ‘presubmission’ List of file for successful regular submission

45. Document Download • Select a document • Press ‘Document Download’ • A ‘Save as’ dialog will be displayed • Choose a location and file name, and save the file • The file will be downloaded and placed in the list of ‘Documents and Reports’ on transaction detail screen • Click on the document in the list to view/open it

46. Document Download (cont.)

47. Troubleshooting • If an error occurs • Locate the client’s log file in cdxclient/log directory on the Desktop(Windows) or home directory(UNIX) • The file is named ‘DNC_NodeClient.log’ • Contact CDX Help Desk or designated technical personnel, attach the log file with your request

48. Help Desk Information • Help Desk Phone Number: 888-890-1995, option 2 • General Help Desk E-mail: helpdesk@epacdx.net • Node Help Desk E-Mail: nodehelpdesk@epacdx.net