# Extensibility Tricks and Tips Paul Brown Chief Plumber

Extensibility Tricks and Tips Paul Brown Chief Plumber INFORMIX Software What we will talk about: Three Common Problems Parts explosion or bill of materials Dynamic SQL in stored procedures Temporal data and temporal SQL Solutions Using OR Techniques Explanation: How each solution works Demo: What each solution looks like Software: Where you can get it Notes on Philosophy What this says about how to use OR technology Informix user .conference 2 Parts Explosion/ Bill of Materials The Problem Parts table, parent part column How many parts in part X? style queries Only solution involves walking the hierarchy in middle-ware or in an iterative join

Relational Solution Walk the hierarchy Slow, cumbersome, code intensive Implementing acyclic constraint constraint very difficult 1 2 6 3 7 Id 1 2 3 4 5 6 7 8 9 10 4 8 P_Id

1 1 1 1 2 2 4 4 4 9 5 10 Name Door Hinge Panel Lock Frame 6 Screw Joint Door Knob Lock Informix 4 Screw user .conference

Object-Relational Solution Graph Theory Trick: 1.0 Node data type {N.N.N.N} Mathematical operators { <, <=, =, =>, > } Single method Incr() Incr(N.N.N) = N.N.(N+1); Implement as UDT: Use B-tree to index Ids Primary key prevents cyclicity How many parts make up the Lock? query shown. 1.1 1.1.1 Id 1.0 1.1 1.2 1.3 1.4 1.1.1 1.1.2 1.3.1 1.3.2 1.3.3

1.2 1.1.2 Name Door Hinge Panel Lock Frame 6 Screw Joint Door Knob 4 Screw 1.3 1.3.1 1.3.2 1.4 1.3.3 SELECT COUNT(*) FROM Parts P1, Parts P2 WHERE P1.Name = Lock AND ( P2.Id BETWEEN P1.Id AND Incr ( P1.Id ) );

Informix user .conference Performance study Experimental results Implemented as relational Implemented as objectrelational 10 level deep hierarchy, 6 wide How many objects under 1.2.3? SELECT COUNT(*) FROM TestTable T WHERE T.Node BETWEEN 1.2.3 AND Incr(1.2.3); CREATE FUNCTION Under( Arg1 INTEGER ) RETURNING INTEGER; DEFINE nRetCount INTEGER; DEFINE nRows INTEGER; LET nRetCount = 0; CREATE TEMP TABLE _Under_Temp_1 ( Id INTEGER NOT NULL ); CREATE TEMP TABLE _Under_Temp_2 ( Id INTEGER NOT NULL );

INSERT INTO _Under_Temp_1 SELECT Id FROM TestTable WHERE Parent = Arg1; LET nRows = DFINFO('sqlca.sqlerrd2'); 6000000 4000000 2000000 0 Relational Object-Relational WHILE (nRows > 0) 400 LET nRetCount = nRetCount + nRows; DELETE FROM _Under_Temp_2 WHERE 1 = 1; 200 INSERT INTO _Under_Temp_2 SELECT * FROM _Under_Temp_1; DELETE FROM _Under_Temp_1 WHERE 1 = 1; INSERT INTO _Under_Temp_1 SELECT T.Id FROM _Under_Temp_2 N, 0 TestTable T Relational Object-Relational WHERE N.Id = T.Parent; LET nRows = DFINFO('sqlca.sqlerrd2'); END WHILE;

5.5 Million vs. 287 Secs vs 27K BufReads 4 Secs Informix user .conference DROP TABLE _Under_Temp_1; DROP TABLE _Under_Temp_2; RETURN nRetCount; END FUNCTION; Dynamic SQL in Stored Procedures One of the MRF ( Most Requested Features) Variable Table/Column Names Please Something other DBMS products have Workarounds Laborious: SYSTEM ( CREATE PROC) Do it in the external program (client or app server) Solution Points to Bigger Issue Using Extensibility in SPL Other Examples: Isplit() EXECUTE FUNCTION Isplit(Hello World, ); Hello World

7 Informix user .conference Architectural Model (1) EXECUTE FUNCTION TableRowCount ( Foo ); (3) Run Query TableRowCnt (Tname VARCHAR) RETURNS INTEGER DEFINE nRetVal INTEGER; DEFINE lvQuery LVARCHAR; LET lvQuery = SELECT COUNT(*) FROM || Tname || ; LET nRetVal = EXEC( lvQuery); RETURN nRetVal; EXEC() (2) EXEC(SELECT COUNT(*) FROM Foo;) Informix user .conference 8 DEMO Informix user .conference 9

Time and T-SQL SQL-92 Has Atomic and Floating Time DATE and DATETIME INTERVAL Other Time Concepts Period: Fixed interval in the time line Hard because of Overlap() queries Before ( A, B ) B A Contained( D, E) Contains (E, D) After ( C, B ) C D F E Overlaps (F, G) G Time Line 10

Informix user .conference Why is this tricky? Query Expressions are Awkward SELECT T.Train_Id FROM Train_Schedules T WHERE NOT ( ( T.End < :StartTime ) OR ( T.Start > :StopTime ) ); Indexing is a Big Problem B-Tree < End, Start > or B-Tree < Start, End > For many queries, planner must scan Informix user .conference 11 Time and T-SQL: ORDBMS Solution Period and DT_Period as New Types Set of UDFs { Overlap(), etc } for queries R-Tree indexing support functions CREATE TABLE Train_Schedule ( Train Train_Id Track Track_Id When DT_Period Train

Toot-1 Toot-1 Toot-2 Track 102 103 102 NOT NULL, NOT NULL, NOT NULL ); When 07/07/2000 12:31:00 to 07/07/2000 12:35:00 07/07/2000 12:35:00 to 07/07/2000 12:40:00 07/07/2000 12:37:00 to 07/07/2000 12:45:00 Informix user .conference 12 Some Notes on Philosophy Object-Relational Not Just Media Stuff Uses in Industrial applications Extend SQL with new functionality Raises Abstraction Level in Data Model Objects in a relational data model Standards Emerging: SQL-3, SQL-J/JDBC 2.0 Informix

user .conference 13 Where can I get this stuff? These Examples and More: http://www.iiug.org/software http://www.informix.com/idn http://examples.informix.com Books on these Topics: Roy, Jacques. Server Side Programming in C: INFORMIX Dynamic Server Prentice Hall. 1999. Brown, Paul. Developing Object-Relational Databases Prentice Hall. 2000. Sanchez, Angela. INFORMIX Universal Server: Best Practices Prentice Hall. 1997. Informix user .conference 14

## Recently Viewed Presentations

• An orderly world where all involved are fulfilling a role. The young people are preforming their role- that of a student & the school is performing its task of preparing students to be productive citizens in society
• Imagism and Vorticism "In a poem of this sort ["In a Station of the Metro or other imagist works], one is trying to record the precise instant when a thing outward and objective transforms itself, or darts into a thing...
• Webspiration—online version of Inspiration Inspiration—website with standards match Planets --Inspiration Inner and Outer Planets--Word Template: 4-Dimensional Vocabulary (describes what belongs in each section of the template) 4-Dimensional template using the word ban - Webster's Dictionary--use for definition Gardner's Theory of...
• Draw Polyatomics Ammonium Sulfate Types of Covalent Bonds NON-Polar bonds Electrons shared evenly in the bond E-neg difference is zero Between identical atoms Diatomic molecules Types of Covalent Bonds Polar bond Electrons unevenly shared E-neg difference greater than zero but...
• Allport's Trait Theory: questioned Freud's view of unconscious in personality; believed individual's personalities were unique. Cattell's Factor Analysis: proposed that some traits can predict other traits; came up with 16 key factors to describe personality
• Etat des lieux du réseau cyclable blagnacais Gaël DUREAU mercredi 1er oct 2008 Sommaire 1/ Définitions 2/ Etat des lieux cyclables Liaisons intercommunales [LIC] Discontinuité communales [DSC] Intégration des équipements publics [IEP] Intégration des pôles commerçants [IPC] Intégration des pôles...
• www.ibew-ewmc.com . History. The EWMC is a strong advocate for equal rights, opportunities, and greater minority representation in the IBEW. The EWMC is well respected by the IBEW and the labor movement because of its strong commitment and vigorous pursuit...
• A chart of MPEP sections affected by the 2019 PEG will be posted on the microsite. The 2019 PEG also supersedes all versions of the USPTO's "Eligibility Quick Reference Sheet Identifying Abstract Ideas" (first issued in July 2015 and updated...