Transcript UIC Data Conversion and Submission

UIC Data Conversion and
Submission via CDX Node
Client
UIC Database V2
6/16/2010
1
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)
2
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
3
MS ACCESS Configuration
4
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)
5
Configuration (MS Access) (cont.)
6
Configuration (MS Access) (cont.)
• open the uic-ds.xml file in your favorite text (or
XML) editor
7
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
8
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
9
Configuration (MS Access) (cont.)
10
Configuration (MS Access) (cont.)
Click on the ‘Add…’ button
11
Configuration (MS Access)
(cont.)
• Select ‘Microsoft Access Driver’ and press
the ‘Finish’ button
12
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)
13
• Press ‘OK’ button on ‘Select Database’ screen
Configuration (MS Access)
(cont.)
• Press the ‘Advanced…’ button
14
Configuration (MS Access)
(cont.)
• The ‘Set Advanced Options’ screen will pop
15
up
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 uicds.xml file
• Press ‘OK’ on all the ODBC related open screens
16
ORACLE Configuration
17
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>
18
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
19
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
20
MS SQL SERVER Configuration
21
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>
22
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
23
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
24
How to Use Node Client
(Client)
25
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
26
Running the Client (cont.)
27
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
28
Running the Client (cont.)
• Type in your NAAS user id and password
• Press the ‘Login’ button
29
Running the Client (cont.)
• Upon successful login, the client’s main
screen will be displayed
30
Converting and Submitting data
• Select the ‘Submit via Conversion Service’
menu item from the ‘File’ menu
31
Converting and Submitting Data (cont.)
• The ‘Submit via Conversion Service’ screen will be
displayed
32
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
33
selected node upon conversion
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
34
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
35
Converting and Submitting data(cont.)
36
After Submission – check
transaction status
• Press the ‘Check Transaction Status’ button to
update status of the transaction from CDX Node
37
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.
38
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
39
Transaction Details
40
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
41
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
42
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 43
Documents Under Transaction
(cont.)
List of file for ‘presubmission’
List of file for successful regular
submission
44
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
45
Document Download (cont.)
46
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
47
Help Desk Information
•
Help Desk Phone Number: 888-890-1995, option 2
•
General Help Desk E-mail: [email protected]
•
Node Help Desk E-Mail: [email protected]
48