August 14, 2007
+1 at 1rst Annual Southern California Oracle Users Conference
+1 Software Engineering attends the 1rst Annual Southern California Oracle Users Conference.
Over 500 attendees, some from as far away as India, attended conference in Long Beach, California.
Ken Jacobs and Tom Kyte both Vice Presidents at Oracle made keynote presentations on Oracle 11g.
Click here to view +1 Software Engineering's conference handout distributed on
+1Copybook and +1DataElements.
|
August 2007
+1Copybook Supports OCCURS Statements
+1Copybook lets you load a single row of data into one or more Oracle or SQL Server
tables when OCCURS are defined by allowing you to define CHILD tables.
For example, below is part of a COBOL copybook defining CUSTOMER data.
By adding "CHILD ONE" and "CHILD END" around the OCCURS statements,
+1Copybook will generate the CREATE TABLE script and control files for both the
CUSTOMER and ONE tables.
Only non-NULL occurances of GED-USER-SCORE and GED-USER-DATE are loaded into the ONE table.
...
CHILD ONE
000100 03 GED-USER-CR-SCORE-DATA OCCURS 3 TIMES.
000100 10 GED-USER-CR-SCORE-DATA OCCURS 5 TIMES.
000100 15 GED-USER-SCORE PIC X(03).
000100 15 GED-USER-DATE PIC S9(07).
CHILD END
...
|
May 18, 2007
+1 Makes Presentation to the Los Angeles Oracle User Group
+1 Software Engineering makes a technical presentation on +1Copybook to the
Los Angeles Oracle User Group.
Click here to view our narrated +1Copybook presentation.
For information on LAOUG, please visit the LAOUG web site.
|
April 2007
+1DataElements Now Generates Data-driven Data Element Dictionary
What is a data element dictionary?
For most database projects, a data element dictionary describes each table and each column
found in the table.
The person in charge of creating the data element dictionary might have been given
a template describing which fields are required.
The person runs some queries, copy and pastes the output into a Word document, reads or asks
about what values the database can have, and then creates the data element dictionary.
This is a very time consuming task.
The resulting document should be easily accessible to others, but rarely is.
If the dictionary exists at all, this document tends to be large and out of date,
most likely due to massive effort it takes to keep it up to date and no way of actually
knowing if the final document does indeed define all tables and column values
that actually exist in the database.
+1DataElements helps to automate the task by first querying the tables found in the database to
list the columns for each table.
Users can then generate template files for each table.
For each table, a template allows the user to write a table description or specify a URL.
For each column, the user can write a column description, specify a URL, and for distinct columns,
specify each possible value and the value's meaning.
If there is a standard template, you can customize what attributes need to be added to the templates.
In addition, +1DataElements generates HTML output which allows the user to easily navigate to the
table and a column in a table.
+1DataElements can generate a script to extract information from the database itself.
For columns with a distinct number of values (a number which you can specify),
+1DataElements will include the column name, data type, min value,
max value, frequent value, number of distinct values,
and the actual values found in the database.
+1DataElements can be run at anytime to merge your written documentation with the
latest values and statistics.
+1DataElements also supports the undefined option ("-u") which tells you what you don't know about your database!
For example, a column may have a new value defined which is not defined in the data element dictionary.
+1DataElements can highlight this value and mark it as undefined.
You can run this option against an undocumented database and all values plus table descriptions
will be flagged as undefined initially.
After you have defined each value, you can rerun +1DataElements to ensure
you have documented every value in the database.
Verifying that your data element dictionary is complete simply isn't possible when the dictionary is found
in a Word document.
The
+1DataElementsTM Users Guide has been updated and contains a number of examples
in the appendices on how to use +1DataElements.
|
September 2006
Release C2 Beta Now Available For +1Copybook and +1DataElements
+1Copybook Users Guide For SQL Server - Release C2 Beta
New features found in Release C2 Beta are SQL Server enhancements:
- +1Copybook now checks and reports the following errors:
- Check for blanks inbetween numeric data, e.g., 12 34.
- Check for invalid signed data character.
- Check for characters in numeric columns, e.g., 12A34.
- Check that the copybook defined length equals the first line of data length.
- Check for field delimiter character in VARCHAR columns.
- Added "use database_id" and BULK INSERT commands to CREATE_TABLE scripts.
- New options supported for +1DataElements are:
| -C 999 |
Create comment PLUS1_EXTRACT.sql script used to extract database attributes, such
as data type, maximum, minimum, frequent, infrequent, and distinct values (with descriptions).
The specified number indicates the number of distinct values to list in report.
|
| -R 999 |
Load only row number 999 in data.
SQL Server can generate load errors for one or more identified row numbers.
This option is useful to load in just one row to more quickly debug what the problem might be.
The HTML copybook map displays the row data divided into its column values.
|
| -T |
Create comment templates in comments/template.dir directory. |
| -U |
Create unknown values report. Lists all unknown values in report. |
- New configuration file named dos_files.txt introduced. (This file replaces dos_file.txt introduced in
previous release.)
The dos_files.txt has two lines.
First line is the DOS path to the location where data files are located.
Data files can end in .txt, .dat, or .asc.
The second line is the DOS path to the location where format files are located.
Data and format files can now be located anywhere on system.
Both test and production dos_files.txt files can be created.
- Support for NO_DELI and NO_COMMENTS files.
If the NO_DELI file is seen, the delimiter files are not created.
If the NO_COMMENTS file is seen, no comments are created.
If previous delimiter files and/or comments have been generated, they are still accessible.
To create these files, use the "touch NO_DELI" or "touch NO_COMMENTS" commands.
Corrections made from previous C Beta release:
- Corrected an error on how some implied decimal point data is written to delimiter files.
- Corrected error for -# option. Line delimiter wasn't written correctly.
|
July 2006
Release C Beta Now Available For +1Copybook and +1DataElements
+1Copybook Users Guide For SQL Server - Release C Beta
New features found in Release C Beta are SQL Server enhancements:
- +1Copybook now supports signed values and implied decimal points.
- +1Copybook divides and displays the first line of data based on the COBOL copybook file layout.
- +1Copybook allows you to pinpoint which column is the source of loading errors.
Additional information on these new features:
- +1Copybook now supports signed values and implied decimal points.
A signed column can be defined as PIC S9(05) and an implied decimal point can be defined as PIC 9(05)V9(02) in
the COBOL copybook.
A column may also be both signed and contain an implied decimal point, such as PIC S9(05)V9(02).
When +1Copybook sees either a signed or implied decimal point in the COBOL copybook, it transforms
the data found in fixed width input and creates a file with the transformed data with field delimiters.
These files end with ".deli" for delimiter files.
If a copybook does not contain signed or implied decimal points, delimiter files are not created.
-
+1Copybook parses the first line of data and displays this data as defined by the COBOL copybook.
For example, if the COBOL copybook contains id, first_name, and last_name as the first three columns,
+1Copybook may display "3423456", "John", and "Day" as the values in the HTML mapping file.
Viewing the actual data for the first line of data
helps in locating possible errors when loading in data.
For example, if you see characters in a numeric column,
numbers with spaces inbetween, characters not aligned with their fields, or blanks in numeric columns,
it may indicate the COBOL copybook definition doesn't match the data.
-
When loading in data, sometimes SQL Server will generate what we call a "global error," which
reports an error but doesn't tell you which column being loaded generated it.
Trying to locate which column is the source of the error can take a long time.
To help, +1Copybook now supports the "-#" option.
With this option, you can load in 'n' number of columns.
For example, say there is 100 columns in the table and SQL Server tells you a loading error has occurred
but doesn't tell you which column is the problem.
Using +1Copybook, you can specify "-# 50" to load in the first 50 columns of the table.
If the error is still generated, you can try "-# 25".
If the error is not generated, you can try "-#75".
You can increase or decrease the number of columns to load until you locate the column
which is causing the error.
New options are:
New configuration files:
New directories:
May 2006
Release B Now Available For +1Copybook and +1DataElements
New features found in Release B:
- +1Copybook defaults to generating CREATE TABLE scripts. You no longer need to specify the "-c" option.
- +1Copybook now uses the "-c" option to perform compare analysis to ensure all COBOL copybook
columns are used and all Oracle columns are initialized when +1Copybook is used with existing tables.
- Both products support new comments/*.txt configuration files to support application specific
HTML documentation.
- +1DataElements Scripts is a new manual.
This manual provides an example on how to automatically generate HTML documentation for three databases
on a regular basis using cron.
- Both products, as well as all user guides, now support HTML Cascading Style Sheets (CSS) formatting.
- Both products allow you to view "All Tables", "All Tables Sorted", and if comments/*.txt
files are used, "All Comments"
links.
- For SQL Server, +1Copybook supports the delimiters.txt file to define field and row delimiters
for format files.
- Both products specify alphabetical release information in CREATE TABLE and control file scripts.
- All user guides were updated for this release.
- And more!
Additional information on these new features follows:
-
The "-c" option no longer needs to be typed to generate CREATE TABLE SQL scripts.
This is now the default.
We did this to make using +1Copybook easier to use.
To generate control files and CREATE TABLE scripts, users only need to create a copybook.txt file,
which is an easy task, and type "cb".
-
The "-c" option is now used to perform compare analysis.
Compare analysis checks every COBOL copybook column is used to initialize an Oracle column and
every Oracle column is initialized by a COBOL copybook column.
This rather extensive analysis is rarely done when control files are generated by hand.
With this analysis which is performed in seconds,
+1Copybook is able to identify probable and time consuming errors before data is loaded.
-
+1DataElements support comments/*.txt files to allow you to document a database from an application programmer's view.
For tables, these files allow you to write a table description and specify a URL.
For individual columns, these files allow you to write a description, specify a URL, specify a column format,
and define possible column values matched with a value description.
Table and column descriptions can be up to 4,000 characters, the Oracle maximum for comments.
+1DataElements generates SQL scripts to comment the tables and columns in the database.
This allows you to document a database without requiring +1DataElements to support the database if desired.
The +1DataElements "-t" and "-T" option are new and lets you create comments/*.txt file templates.
Since most databases are not documented from an application view using HTML,
this feature can differentiate your company from the competititon when trying to win new business.
Using comments/*.txt files, application programmers can:
- quickly learn what the table or column is,
- be linked to URLs which can point directly into standard documents found on the Internet or on a company's
internal web site describing the column,
- define the format used for a column like MM-DD-YYYY or DD-MON-YYYY, and
- define a column's values with their definitions, e.g.,
a 01 is an emergency room visit while a 02 is a physician visit.
By providing database information for an individual column, one table, or all tables in a database
to the application programmers,
development, maintenance, and debugging times all decrease as
it is faster to locate needed information to understand and resolve any problems.
-
+1DataElementsTM Scripts is a new manual.
This manual documents the t.sql, i.sql, and v.sql scripts used to create
the tables.txt, indexes.txt, and views.txt files.
+1DataElements uses these .txt files to generate HTML documentation for existing databases.
The run_de.sh shell script provides an example on how +1DataElements can be used to
generate HTML documentation automatically on a regular basis for three Oracle databases using
cron to determine when HTML updates should occur.
We encourage all users to try out this feature.
-
HTML output from +1Copybook and +1DataElements, as well as all of our user guides, are now written
using Cascading Style Sheets (CSS).
-
At the table index level, you can now list "All Tables" and "All Tables Sorted".
If using comments/*.txt files, you will also see "All Comments" too.
- For SQL Server, +1Copybook now supports the delimiters.txt file to define field and row delimiters
for format files.
-
+1 Software Engineering now alphabetizes its releases.
For this release, the generated control file and CREATE TABLE scripts will show:
Created by: +1Copybook (B)
This lets you see what version of our products were used to generate these files.
By using letters instead of a release date, there is no confusion with the "Created on:" date value.
To see the date all versions were released, you can use the "-V" option.
- For +1Copybook, the "-m" option has been reversed.
Previously the "-m" was used to display the CREATE TABLE data type column in the copybook mapping output.
This is now the default.
Today, the "-m" option is used to not display the CREATE TABLE data type column in the copybook mapping output
if desired.
- New options for +1Copybook are:
- New configuration files for +1Copybook are:
- New options for +1DataElements are:
- New configuration files for +1DataElements are:
|
|
