1 / 18

Simple Shared Metadata

Innovative Routines International (IRI), Inc. The CoSORT Company. Simple Shared Metadata. About Us Innovative Routines International (IRI), Inc. Founded 1978, New York Released world’s 1 st commercial sorts for: CP/M, DOS, UNIX, Windows, Linux

karleigh-vinson
Download Presentation

Simple Shared Metadata

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Innovative Routines International (IRI), Inc. The CoSORT Company Simple Shared Metadata

  2. About Us Innovative Routines International (IRI), Inc. • Founded 1978, New York • Released world’s 1st commercial sorts for: CP/M, DOS, UNIX, Windows, Linux • 30+ Int’l Agencies, Profitable since inception • Core products: CoSORT, FACT, RowGen • Fast Sorts, ETL, Reorg, Reports, Test Data • CoSORT recommended by all H/W OEMs • Embedding by/with top ISVs: Acucobol, AMS, Cincom, Experian, IVIS, Kalido, Micro Focus, Sabre, Soliton, Sun, Valdero, ViPS, etc. CoSORT/ IRI, Inc. 2194 Highway A1A, Suite 303 Melbourne, FL 32937-4932 USA www.cosort.com 1-800-333-SORT

  3. Optimizing Data Population & Presentation “The Customer Intelligence Lifecycle” IRI softwarestages, reports and synthesizes large data volumes outside the database. FACT, CoSORT, and RowGen leverage the speed and simplicity of sequential (flat) file processing.

  4. Perform and Speed Oracle Reorgs and ETL ! E T L CoSORT’s FAst extraCT (FACT) for Oracle CoSORT’s Sort Control Language(SortCL) SQL*Loader

  5. CoSORT’s FAst extraCT (FACT) for Oracle ETL Faster Oracle Unloads • Rapidly Dumps Table Contents (Extract) • Parallel hints, SQL syntax, flat file results! • Creates CoSORT/SortCL Metadata (Transform) • Select, join, sort, aggregate, calculate, convert, and re-format • Creates SQL*Loader Metadata (Load) • Pre-sorted flat files can re-populate Oracle up to 90% faster • Combine all 3 into 1 Fast E-T-L Pass! Ask for demo

  6. Fast data integration & staging (Transformation)all-in-one I/O pass … ETL CoSORT Sort Control Language(SortCL) … through many, large, differently-formatted inputs: • Selectvia record filters or conditional include/omit • Sort / Mergeany number of keys in any position, 2GB+/min • JoinSQL match syntax 1-1, many-1, inner & outer • Converttranslate input field data types to new types • Aggregatemin, max, average, sum, count (sub and grand) • Calculateacross rows to perform math (+ sci functions) • Re-mapchange field positions, sizes, and values • Reportto highly-formatted, multi-level output targets • User Exitsfor custom input, compare and output criteria

  7. IRI’s FACTextracts Oracle tables, and creates metadata for [simultaneous] CoSORT/SortCL transformand [pre-sorted]SQL*Loader bulk load operations. All you do is create the FACT and SortCLE and T config. files, and watch it fly! See demo.

  8. Oracle9i Table: “Orders” ORDER CUSTOMER EMPLOYEE SHIP_DATE FREIGHT ----- ----------------------------------- ----------------- --------- ---------- 11040 Great Lakes Food Market Peacock, Margaret 18.84 11041 Chop-suey Chinese Leverling, Janet 28-APR-98 48.22 11042 Comeio Mineiro Fuller, Andrew 01-MAY-98 29.99 11043 Spealitedu monde Buchanan, Steven 29-APR-98 8.8 11044 Wolski Zajazd Peacock, Margaret 01-MAY-98 8.72 11045 Bottom-Dollar Markets Suyama, Michael 70.58 11046 Die Wandernde Kuh Callahan, Laura 24-APR-98 71.64 11047 Eastern Connection King, Robert 01-MAY-98 46.62 11048 Bottom-Dollar Markets King, Robert 30-APR-98 24.12 11049 Gourmet Lanchonetes Leverling, Janet 04-MAY-98 8.34 11050 Folk och faB Callahan, Laura 05-MAY-98 59.41 ORDER CUSTOMER EMPLOYEE SHIP_DATE FREIGHT ----- ----------------------------------- ----------------- --------- ---------- 11051 La maison dAsie King, Robert 2.79 11052 Hanari Carnes Leverling, Janet 01-MAY-98 67.26 11053 Piccolo und mehr Fuller, Andrew 29-APR-98 53.05 11054 Cactus Comidas para llevar Callahan, Laura .33 11055 HILARIO-Abastos King, Robert 05-MAY-98 120.92 11056 Eastern Connection Callahan, Laura 01-MAY-98 278.96 11057 North/South Leverling, Janet 01-MAY-98 4.13 11058 Blauer See Delikatessen Dodsworth, Anne 31.14 11059 Ricardo Adocicados Fuller, Andrew 85.8 11060 Franchi S.p.A. Fuller, Andrew 04-MAY-98 10.98 11061 Great Lakes Food Market Peacock, Margaret 14.01

  9. FAst extraCT (FACT) .ini File # "orders.ini" # FACT initialization file for ETL example # gets run with fact emp.ini DATABASE=ORACLE INSTANCE=test USERID=scott PASSWORD=tiger QUERY=SELECT * FROM orders OUTFILE=stdout # extracted records pipe out to SortCL; metadata in stdout.ddf VARIABLE DELIM=, FRAMEFIELD=EMPLOYEE # Specifies the name of the column to be framed FRAMECHAR=" # Framed column to be enclosed in double quotes FETCHSIZE=autoLOADTABLE=orders_sorted LOADTYPE=TRUNCATE # sorted records from table ‘orders’ will insert into new table DATAEXT=.dat CTLEXT=.ctl DDFEXT=.ddfREPORTEXT=.log DATEFORMAT=YYYY/MM/DD

  10. CoSORT (SortCL) File Metadata [cosort@demo example]$ cat stdout.ddf /FILE=stdin /FIELD=(ORDER_ID,POS=1,SEP=',') /FIELD=(CUSTOMER,POS=2,SEP=',') /FIELD=(EMPLOYEE,POS=3,SEP=',',FRAME='"') /FIELD=(SHIP_DATE,POS=4,SEP=',') /FIELD=(FREIGHT,POS=5,SEP=',',NUMERIC) Notes: 1) FACT created this metadata during the Oracle table unload 2) stdin will be the piped input into CoSORT’sSortCLprogram 3) /FIELD statements define each fixed/floating data column 4) SortCLuses field names for transforms and output remapping 5) Centralized metadata can be (re)used in SortCL & RowGen apps 6) MetaIntegration tool users can also build .ddf’s automatically!

  11. CoSORT (SortCL) Application [cosort@demo example]$ cat orders_sorted.scl /INFILE=stdin /SPEC=stdout.ddf # FACT-created metadata description /CONDITION=(change_emp_cust,TEST=(EMPLOYEE OR CUSTOMER)) /SORT# Pre-sorted loads are faster! /KEY=CUSTOMER # 2-key sort, ascending /KEY=SHIP_DATE /OUTFILE=stdout.dat # Streaming output to SQL*Loader /OUTFILE=orders.csv # Outfile 2: Table replication (sorted) /OUTFILE=orders.sum # Outfile 3: Freight total report /SPEC=order2.scl # Metadata for SortCL aggregation app Notes: 1) All SortCLscripts flow this way: Input Process Output 2) stdin is the piped input from FACT’s table unload 3) /SPEC references /FIELD and other SortCL metadata syntax 4) SortCL can map >1 inputs & formats to >1 outputs & formats

  12. Single-Pass Oracle ETL Operation [cosort@demo example]$ vi orders_ETL.sh rm -r stdout.dat mkfifo stdout.dat fact orders.ini info_file fact orders.ini | sortcl /spec=orders_sorted.scl & sqlldrscott/tiger control=stdout.ctl DIRECT=TRUE~ Notes: 1) rm -r stdout.dat clears any same-named prior FIFO buffer 2) mkfifo stdout.dat creates a new FIFO buffer for stdout.dat 3) fact info_file orders.ini creates both metadata and log files 4) fact orders.ini extracts and reformats table data (see .ini file) 5) | sortcl drives FACT-unloaded data into CoSORT (SortCL) 6) & sqlldr is named pipe from CoSORT (SortCL) to SQL*Loader

  13. CoSORT’s RowGenData Synthesizer ETL New Product! Build Your Own Custom Files • Prototype Applications • Create data and file formats your projects need • Share File Formats • Provide accurate layouts without real data • Specify Value Ranges • Use selection and set files for realistic data • Simulate DB Ops • Quickly test table loading and query scenarios • Benchmark Testing • Gen big files for hardware and software PoCs

  14. SortCL & RowGen: Same Metadata! /INFILE=orders_data # source (SortCL) / placeholder (RowGen) # layouts for: input data (SortCL) /generated data (RowGen) /FIELD=(ORDER_ID,set=order_id.set,POS=1,SIZE=5.0,SEP='|',NUMERIC) /FIELD=(CUSTOMER,set=names.set,POS=2,SEP='|') /FIELD=(SHIP_DATE,SET=dates.set,POS=3,SIZE=10,SEP='|',ISO_TIMESTAMP) /FIELD=(ORDER_VALUE,SET=values.set,POS=4,SIZE=6.2,SEP='|',NUMERIC) # set file values for RowGen (ignored by SortCL) /INCOLLECT=252 # 252 records processed (SortCL) / generated (RowGen) /SORT# 2-key sort, ascending /KEY=CUSTOMER /KEY=SHIP_DATE /OUTFILE=customer_orders # names the output target # this layout (1 of 3) derives subtotals /DATA={14}" " /DATA=CUSTOMER /FIELD=(SUM_ORDER_VALUE,POS=46,SIZE=9.2,CURRENCY) /FIELD=(CT_ORDER,POS=58,SIZE=2) /DATA="\n" /DATA={59}"-" /DATA="\n" /SUM SUM_ORDER_VALUE FROM ORDER_VALUE BREAK CUSTOMER /COUNT CT_ORDER BREAK CUSTOMER

  15. Transform & Synthesis MD (Con’t.) /OUTFILE=customer_orders # same file name (format overlay) # this layout (2 of 3) derives grand totals /FIELD=(SUM_ORDER_VALUE,POS=45,SIZE=10.2,CURRENCY) /FIELD=(CT_ORDER,POS=57,SIZE=3) /SUM SUM_ORDER_VALUE FROM ORDER_VALUE /COUNT CT_ORDER /OUTFILE=customer_orders # same file name (format overlay) # this layout (3 of 3) contains detail records /HEADREC=“Ship Date Customer Order ID Value Ct.\ \n-----------------------------------------------------------\n" /FIELD=(SHIP_DATE,POS=1,SIZE=10,ISO_TIMESTAMP) /FIELD=(ORDER_ID,POS=36) /FIELD=(ORDER_VALUE,POS=47,SIZE=8.2,CURRENCY)

  16. Synthesize, Simulate, Share Need Sample Data? RowGen Can Build You: • Simultaneous, multiple target files in 1 or more formats, featuring: • Random field values in >100 data types • ASCII upper/lowercase, EBCDIC, COBOL, IP Addresses, Timestamps, etc. • Popular file formats • CSV, Line / Record / Variable Sequential, MF ISAM, ELF, Unisys VBF, etc. • Randomly-selected data from real “set” files, incl. DB extracts • Conditional and duplicate filters + custom output mapping (data segmentation) • Detail records for aggregation, with simultaneous check file(s) created • Sorting, aggregation, and cross-field expressions (vertical and horizontal calcs) • Numeric fields in precisely-defined ranges and layouts • Multiple record layouts in one or more simultaneous targets • Using same metadata as/for SortCL transforms and reports! • New target data in the same file format (via random seed values) • Mixed valid and invalid records (to test app filtering) • Realistic, structured, fully-custom report formats

  17. Innovative Routines International (IRI), Inc. The CoSORT Company cosort.com 1-800-333-SORT

More Related