HP NonStop SQL/MX Queuing and Publish/Subscribe Services Abstract HP NonStop™ SQL/MX provides notification of database changes by integrating transactional queuing and publish/subscribe services into its database infrastructure. This manual describes these extensions to the Data Manipulation Language (DML) of SQL/MX. Product Version NonStop SQL/MX Release 2.0 Supported Release Version Updates (RVUs) This publication supports G06.
Document History Part Number Product Version Published 424298-001 NonStop SQL/MX Release 1.0 February 2001 520561-001 NonStop SQL/MX Release 1.5 November 2001 523733-001 NonStop SQL/MX Release 1.8 December 2002 523734-001 NonStop SQL/MX Release 2.0 April 2004 523734-002 NonStop SQL/MX Release 2.
HP NonStop SQL/MX Queuing and Publish/Subscribe Services Index Examples What’s New in This Manual v Manual Information v New and Changed Information Tables v About This Manual vii Audience vii Related Documentation vii Notation Conventions xi 1.
3. Embedded SQL Examples Contents Ordered Streams 2-14 Index Requirements 2-14 Order by a Column Value 2-14 Order by the SYSKEY Value 2-15 Joins 2-15 Run-Time Limits on Streams 2-16 Restarting a Subscriber 2-17 Destructive Subscribers 2-17 Nondestructive Subscribers 2-17 3.
Index Contents Scenario 3. Invoice Processing by Priority B-6 The Invoices Table B-6 Dequeueing Invoices by Priority B-6 Scenario 4. Fetching Contract Data B-8 The Invoices Table B-8 The Contracts Table B-8 Joining Invoices With Contracts B-9 Scenario 5. The Shipping Application B-9 The Inbox Table B-10 Inserting Notifications of Incoming Parcels B-10 Subscribing to Route Incoming Parcels B-10 Using a Holdable Cursor to Efficiently Increase Concurrency B-11 Index Figures Figure 1-1. Figure 1-2.
Contents HP NonStop SQL/MX Queuing and Publish/Subscribe Services —523734-002 iv
What’s New in This Manual Manual Information HP NonStop SQL/MX Queuing and Publish/Subscribe Services Abstract HP NonStop™ SQL/MX provides notification of database changes by integrating transactional queuing and publish/subscribe services into its database infrastructure. This manual describes these extensions to the Data Manipulation Language (DML) of SQL/MX. Product Version NonStop SQL/MX Release 2.0 Supported Release Version Updates (RVUs) This publication supports G06.
What’s New in This Manual • • New and Changed Information Replaced the “Materialize Attribute” heading with Join With a Stream on page 3-8 and removed information about the MATERIALIZE default from this heading. Revised the example in this section to remove references to materialization. Added Considerations for MATERIALIZE, Joins, and Streams on page 4-4.
About This Manual SQL/MX provides immediate notification of database changes by integrating transactional queuing and publish/subscribe services into its database infrastructure. This manual outlines the extensions to the DML of NonStop SQL/MX. It describes how to use the queuing and publish/subscribe features of SQL/MX. The appendixes provide the syntax and semantics for these features and the SQL scripts that create the database used in the examples.
Related Documentation About This Manual Programming Manuals SQL/MX Programming Manual for C and COBOL Describes how to embed SQL/MX statements in ANSI C and COBOL programs. SQL/MX Programming Manual for Java Describes how to embed SQL/MX statements in Java programs according to the SQLJ standard. Specialized Guides SQL/MX Installation and Management Guide Describes how to plan for, install, create, and manage an SQL/MX database. Explains how to use installation and management commands and utilities.
About This Manual Related Documentation The NSM/web and Visual Query Planner help systems are accessible from their respective applications. You can download the Reference, Messages, and Glossary online help from the $SYSTEM.ZMXHELP subvolume or from the HP NonStop Technical Library (NTL). For more information about downloading online help, see the SQL/MX Installation and Management Guide.
Related Documentation About This Manual This figure shows the manuals and online help in the SQL/MX library: Programming Manuals Introductory Guides SQL/MX Comparison Guide for SQL/MP Users SQL/MX Programming Manual for C and COBOL SQL/MX Quick Start SQL/MX Programming Manual for Java Reference Manuals SQL/MX Reference Manual SQL/MX Messages Manual SQL/MX Connectivity Service Administrative Command Reference SQL/MX Glossary DataLoader/MX Reference Manual Specialized Guides SQL/MX Installation
About This Manual Notation Conventions Notation Conventions UPPERCASE LETTERS. Uppercase letters indicate keywords and reserved words. These items are not case sensitive; enter in uppercase or lowercase. Items not enclosed in brackets are required. For example: BEGIN DECLARE SECTION; lowercase italic letters. Lowercase italic letters indicate variable items that you supply. Items not enclosed in brackets are required. For example, :hostvar is required: :hostvar [[INDICATOR] :indicator_var] [ ] Brackets.
About This Manual Change Bar Notation Item Spacing. Spaces shown between keywords are required. Spaces are not required between a punctuation symbol and an item or a delimiter and a keyword. For example: DATEFORMAT ( TIMESTAMP '1996-06-20 14:20:20.00', USA ) or DATEFORMAT(TIMESTAMP'1996-06-20 14:20:20.00',USA) Line Spacing. If the syntax of a command is too long to fit on a single line, each continuation line is indented three spaces.
1 Introduction This section introduces Publish/Subscribe concepts, features, and terms. What Is Publish/Subscribe? Traditionally, relational database systems are passive data stores. Applications can only retrieve and update data. Applications cannot request notification when new data becomes available or when existing data is updated. However, immediate notification of additions and updates to the database is essential for many applications.
Why Use Publish/Subscribe? Introduction of extra performance costs and application complexity. Figure 1-1 shows the processing required. Figure 1-1. Invoice Processing Without Publish/Subscribe Open cursor Fetch invoice No EOF? Yes Close cursor Sleep Update invoice VST002.vsd If you simply started many instances of the application in an attempt to make this application scalable, they would conflict with each other.
Terminology Introduction and update a row in a single call. Again, the application code is simpler and performs better. Figure 1-2 shows the processing required. Figure 1-2. Invoice Processing With Publish/Subscribe Open cursor Embedded UPDATE from stream of invoices VST003.vsd • Publish/subscribe can use a special access mode, skip conflict access. As a result, the various instances of the invoice processing application skip rows that are locked and select and update other available rows.
Traditional Implementation of Publish/Subscribe Introduction Queuing, enqueueing Inserting entries into a queue by using an INSERT or UPDATE statement. Dequeueing Reading and deleting entries with a single operation by using a SELECT statement with an embedded DELETE or UPDATE. This dequeue operation is sometimes referred to as a destructive SELECT. For another description of a destructive SELECT, see Concepts on page 1-5.
Concepts Introduction • • Enable the SQL compiler and SQL system to optimize access to queue entries, notifications, and normal SQL data. Enable scalability and load distribution through horizontal partitioning and fault tolerance through process pair technology. Concepts SQL/MX’s publish/subscribe extensions are based on three fundamental concepts: • • • Streams. Applications perceive queues and channels as continuous data streams.
Comparing Triggers and Publish/Subscribe Introduction Comparing Triggers and Publish/Subscribe SQL/MX Release 2.0 introduces triggers functionality. Both triggers and publish/subscribe services offer ways to make a passive database more active. To decide which feature to use (or whether to use both in the same application), determine the needs of the application. Table 1-1 compares triggers and publish/subscribe. For information about creating or altering triggers, see the SQL/MX Reference Manual.
Comparing Triggers and Publish/Subscribe Introduction Table 1-1. Comparing Triggers and Publish/Subscribe (page 2 of 2) Feature Triggers Publish/Subscribe Number of processes The same number of processes service events and actions. You can scale subscribers independently of publishers (you can program multiple subscribers against one publisher). Events versus actions There is a tight coupling between the number of events and the number of actions.
Introduction Comparing Triggers and Publish/Subscribe HP NonStop SQL/MX Queuing and Publish/Subscribe Services —523734-002 1 -8
2 Major Queuing Features The queuing extensions to SQL/MX are special operations defined on regular database tables. These operations are specified as extensions to the Data Manipulation Language (DML) and do not introduce any new database objects. Applications access tables that are defined by the Data Definition Language (DDL). Publish/subscribe supports only audited tables. The single most important feature for the queuing and publish/subscribe services is the stream access mode.
Stream Access Major Queuing Features Joins on page 2-15 Allows rows from a stream to be joined to a table. Run-Time Limits on Streams on page 2-16 Describes the list used by the Data Access Manager (DAM) to monitor for the stream overflow condition. Restarting a Subscriber on page 2-17 Describes conditions that cause a cursor to close and require subscribers to be restarted. The examples in this section use the scenarios described in Appendix B, Examples.
Major Queuing Features Stream Timeout Attribute For more information about concurrency and defaults, see the SQL/MX Reference Manual. Stream Timeout Attribute Many of the examples in this manual are presented as statements you can execute by using MXCI (the SQL/MX conversational interface).
Embedded DELETE Major Queuing Features An application subscribes and dequeues invoices by issuing this SELECT statement: SET NAMETYPE ANSI; SET SCHEMA psdb.pubs; SELECT * FROM (DELETE FROM STREAM(invoices)) AS invoices; CONTRACTNBR ----------100 200 300 AMOUNT ----------10500 20390 30800 PRIORITY ----------1 2 3 The available rows are retrieved and deleted. The application is now in a wait state until more rows are inserted into the table.
Embedded UPDATE Major Queuing Features If you initiate the transaction explicitly by using BEGIN WORK, after the stream times out, the transaction is still active, and you can either commit or abort: CONTROL QUERY DEFAULT stream_timeout '300'; BEGIN WORK; SELECT * FROM (DELETE FROM STREAM(invoices)) AS invoices; CONTRACTNBR ----------100 200 300 AMOUNT ----------10500 20390 30800 PRIORITY ----------1 2 3 *** ERROR[8006] The stream timed out, but the cursor is still open.
Embedded UPDATE Major Queuing Features From Scenario 2. The Invoices Queue (page B-5), suppose that the invoices table contains a column for archiving: CREATE TABLE pubs.invoices (contractnbr INT, amount INT, priority INT, archive CHAR(1) DEFAULT 'N'); This SELECT statement is similar to the embedded DELETE example but instead uses stream access with an embedded UPDATE operation: SET NAMETYPE ANSI; SET SCHEMA psdb.
Skip Conflict Access Major Queuing Features Skip Conflict Access The skip conflict access method is important in implementing an efficient transactional queuing feature. It prevents concurrent transactions from blocking each other by waiting for the release of locks on rows currently being inserted or updated while other rows are available that are not currently locked by another transaction. Transactional queuing implies that entries are read and deleted with a single operation as part of a transaction.
AFTER LAST ROW Major Queuing Features If a third application enqueues an invoice, either the first or second application can dequeue the new invoice: CONTRACTNBR ----------500 AMOUNT ----------55000 PRIORITY ----------5 Note that if the second application did not use skip conflict access, it would not be able to access this new invoice, because it would be waiting on a locked row and therefore would eventually time out: SELECT * FROM (DELETE FROM STREAM(invoices)) AS invoices; *** ERROR[8551] Error 73
Set Column Values On Rollback Major Queuing Features The stream skips all existing rows and returns rows published after the stream's cursor was opened. Note that in the case where a publisher's transaction was in progress when the subscriber’s statement began, not all the transaction's rows would be returned to the subscriber. Instead only the rows inserted or updated after the statement began executing would be returned.
Holdable Cursors Major Queuing Features Another SELECT statement is issued to dequeue the invoices: SELECT * FROM (DELETE FROM STREAM(invoices) SET ON ROLLBACK abortcount = abortcount + 1 FOR SKIP CONFLICT ACCESS) AS invoices; CONTRACTNBR ----------100 200 300 AMOUNT ----------10500 20390 30800 PRIORITY ----------1 2 3 ABORTCOUNT ---------1 1 0 The first two rows are still in the queue and show the abort count has been incremented.
Major Queuing Features Holdable Cursors EXEC SQL PREPARE :szHoldableStatementName FROM :szHoldableStatement; /* Extended Dynamic Cursor */ strcpy(ext_hold_stmthold, "C1"); EXEC SQL DECLARE :ext_hold_stmthold CURSOR WITH HOLD FOR :szHoldableStatementName; /* Allocated Cursor */ strcpy(ext_hold_stmthold, "C2"); EXEC SQL ALLOCATE :ext_hold_stmthold CURSOR WITH HOLD FOR :szHoldableStatementName; From Scenario 4.
Rowset Integration Major Queuing Features Applications that delete or update many rows, using embedded DELETEs and UPDATEs, experience a significant performance boost if they use a holdable cursor and are coded to avoid closing and reopening the cursor with each COMMIT WORK statement.
Major Queuing Features Partitioned Queues For additional information on how to use rowsets, see the SQL/MX Programming Guide for C and COBOL. Partitioned Queues Partitioned queues and channels allow applications to scale queues and channels across clusters and use data dependent routing to balance the load across the individual devices in a cluster. For example, this CREATE TABLE statement allows partitioning of the inbox table by the value of the destination column: Note.
Major Queuing Features Ordered Streams The start and end values determine which partition of the inbox queue is accessed by the program. To use this method, use start and end values within the range of the destination values for a single partition. Ordered Streams The physical storage order of rows in a key-sequenced table is determined by the clustering key.
Major Queuing Features Order by the SYSKEY Value From Scenario 5. The Shipping Application (page B-9), this SELECT statement uses stream access ordered by priority: SELECT * FROM (DELETE FROM STREAM(inbox) FOR SKIP CONFLICT ACCESS) AS inbox ORDER BY priority; Because streams never return the end-of-data condition, sort operations on userdefined column values cannot be used to sort rows returned from a stream. Instead, the sort order must be materialized by an index.
Major Queuing Features Run-Time Limits on Streams Run-Time Limits on Streams When a row is published into a table that has a cursor opened for stream access, the HP NonStop Data Access Manager (DAM) puts an entry into a list used by the streaming cursor if the cursor's position in the table is logically after the position of the newly published row.
Major Queuing Features Restarting a Subscriber Restarting a Subscriber Certain problems occur that can cause the cursor to close.
Major Queuing Features Nondestructive Subscribers caught up with backlogs. To restart, the operator can start the subscribers (coded with the AFTER LAST ROW clause) first. Application-logic checkpointing provides another approach. To avoid reading rows, you can subscribe with an ORDER BY clause on a unique key. When each row is returned, write the key to a checkpoint file to checkpoint the application’s position.
3 Embedded SQL Examples As described in Section 2, Major Queuing Features, many of the queuing features can be executed within the SQL/MX conversational interface (MXCI). However, to ensure use of all the features, the queuing extensions are embedded in a program. Note that queuing and publish/subscribe services are also supported in Java applications that contain embedded SQL/MX statements. For examples, see the SQL/MX Programming Manual for Java.
Embedded SQL Examples Stream Access Stream Access The stream access mode enables applications to access regular tables as continuous data streams. The stream access mode first does a regular scan of the table. Then, if all available rows have been retrieved, the stream access mode causes fetch operations to wait (block) instead of returning the end-of-data condition.
Embedded DELETE Embedded SQL Examples Embedded DELETE Embedded DELETE statements enable applications to read and delete rows with a single operation. This example shows the use of an embedded DELETE with stream access and a holdable cursor. Note that after each fetch, the application commits its transaction and starts a new transaction before looping to fetch again. --------------------------------------------------------------#include #include
Embedded SQL Examples Embedded UPDATE Embedded UPDATE Embedded update statements enable applications to read and update rows with a single operation. This program illustrates the embedded UPDATE operation within the stream access mode. Suppose that the invoices table, described in Scenario 5. The Shipping Application, contains a column, archive, to mark whether a row has been processed: CREATE TABLE pubs.
Embedded SQL Examples Skip Conflict Access EndOfProcessing: EXEC SQL WHENEVER SQLERROR GOTO EndError; EXEC SQL CLOSE arrivals; EndError: return SQLCODE; } ------------------------------------------------------------ Skip Conflict Access Skip conflict access prevents concurrent transactions from blocking each other while waiting for the release of locks on rows currently being inserted or updated when other rows are available that are not locked.
Set Column Values on Rollback Embedded SQL Examples EndOfProcessing: EXEC SQL WHENEVER SQLERROR GOTO EndError; EXEC SQL CLOSE arrivals; EndError: return SQLCODE; } ------------------------------------------------------------ Set Column Values on Rollback The set on rollback feature enables applications to update columns due to a rollback of a DELETE or UPDATE operation. This program illustrates incrementing the abort count on rollback.
Embedded SQL Examples Rowset Integration EXEC SQL FETCH arrivals INTO :destination,:origin,:seqnbr,:abortcount; printf("\nDestination:%d Origin:%d " "Sequence number:%d Abortcount:%d", destination, origin, seqnbr, abortcount); /* Route package and notify receiver */ routePackage(seqnbr, destination, origin); EXEC SQL COMMIT WORK; EXEC SQL BEGIN WORK; /* Start new transaction */ } EndOfProcessing: EXEC SQL WHENEVER SQLERROR GOTO EndError; EXEC SQL CLOSE arrivals; EndError: return SQLCODE; } ---------------
Join With a Stream Embedded SQL Examples while (1) { /* Dequeue rowset of notifications */ EXEC SQL FETCH arrivals INTO :arrayDestination,:arrayOrigin, :arraySeqnbr; /* Get number of rows fetched */ EXEC SQL GET DIAGNOSTICS :numFetched = ROW_COUNT; printf("\nNumber Fetched: %d",numFetched); /* Process each row fetched in some way */ for (i = 0; i < numFetched; i++) { printf("\nDestination:%d Origin:%d " "Sequence Number:%d", arrayDestination[i], arrayOrigin[i], arraySeqnbr[i]); /* Route package and notify
Embedded SQL Examples Join With a Stream This program illustrates an embedded DELETE accessing a table as a stream, joined to a regular table: --------------------------------------------------------------#include #include
Embedded SQL Examples Managing Transactions With the Stream Timeout Attribute Managing Transactions With the Stream Timeout Attribute A statement selecting from a stream can wait for new rows for a very long duration— hours or even days or weeks. Unless the statement is also using READ UNCOMMITTED access, a transaction will be started by the system, when the statement begins executing, or by the application before execution of the statement. The transaction will consume resources, and locks will be held.
Managing Transactions With the Stream Timeout Attribute Embedded SQL Examples EXEC SQL MODULE cat.sch.
Embedded SQL Examples Managing Transactions With the Stream Timeout Attribute :hv_sqlcode = SQLCODE; hv_sqlstate[5] = 0; printf("condition number: %d\n", hv_cond_num); printf("sqlstate: %s\n", hv_sqlstate); printf("table name: %s\n", hv_table_name); printf("column name: %s\n", hv_column_name); printf("message text: %s\n", hv_message_text); printf("sqlcode: %ld\n", hv_sqlcode); printf("\n"); } /* Special handling for stream_timeout exception.
Managing Transactions With the Stream Timeout Attribute Embedded SQL Examples set to 30 seconds, so that when it has deleted all available rows, the final transaction lasts no more than 30 seconds. --------------------------------------------------------------#include "stdio.h" #include "stdlib.h" #include "assert.h" const int TRUE = 1; const int FALSE = 0; /* This global variable is set to TRUE by the handle_error_or_timeout() function and read and reset to FALSE by the main() function.
Managing Transactions With the Stream Timeout Attribute Embedded SQL Examples { exec sql begin work; exec sql control query default STREAM_TIMEOUT '3000'; exec sql select a into :hv_a from (delete from stream(t) where a >= 0 for skip conflict access) as t; exec sql commit; if (timed_out == TRUE) { timed_out = FALSE; /* break to outer loop */ break; } else { printf("%d dequeued.
Embedded SQL Examples Managing Transactions With the Stream Timeout Attribute printf("column name: %s\n", hv_column_name); printf("message text: %s\n", hv_message_text); printf("sqlcode: %ld\n", hv_sqlcode); printf("\n"); } /* Special handling for stream_timeout exception. */ if (hv_sqlcode == -8006 && num == 1) { printf("Stream timeout.\n"); timed_out = TRUE; return; } exit(1); err_exit: printf("Error in the error handler.
Embedded SQL Examples Managing Transactions With the Stream Timeout Attribute HP NonStop SQL/MX Queuing and Publish/Subscribe Services —523734-002 3- 16
4 Performance Considerations This section describes performance issues for applications that use publish/subscribe services. This section also provides tips to help you maximize performance. Considerations for Partitions • Avoid using too many subscribers on a partition. You can configure multiple subscribers on a partition, but if you configure too many, especially subscribers using destructive SELECTs, they will tend to block each other.
Performance Considerations Considerations for Embedded UPDATEs feature. For information about the TMF autoabort feature, see the TMF Reference Manual. Considerations for Embedded UPDATEs • Be aware of a performance issue with embedded UPDATEs. You might want to use publish/subscribe services to model a queue, so that a published row is returned to only one subscriber. To do this, you can use an embedded UPDATE statement, as described in Section 2, Major Queuing Features.
Performance Considerations Considerations for Rowsets When the subscriber processes the work, it deletes the row from the WORK_PENDING_QUEUE with an embedded DELETE. This row can be joined to the master table with this syntax: select a, b, c from (delete from stream(work_pending_queue)) as q, master m where q.rowid = m.a; Considerations for Rowsets • Use rowsets with destructive SELECTs. Embedded UPDATEs and DELETEs typically send and receive one file system message per SQL fetch operation.
Performance Considerations Considerations for MATERIALIZE, Joins, and Streams This example creates the workaround: create table t1_with_dummy ( a int not null, b int, c int, primary key(a)); insert into t1_with_dummy values (-1, 0, 0); create view t1(a,b,c) as select a, b, c from t1_with_dummy where a <> -1; This example shows how to use the workaround: exec sql declare c1 cursor with hold for select a, b, c from (delete from stream(t1)) as t1; Considerations for MATERIALIZE, Joins, and Streams The MATER
A The Shipping Database The examples presented in this appendix use tables created by the execution of SQL/MX CREATE TABLE statements. For further information about this statement, see the SQL/MX Reference Manual. The SQL/MX DDL and SQL/MX INSERT statements provided in this appendix enable you to create and populate the queueing and publish/subscribe database so that you can use the SQL/MX features described in this manual. CREATE CATALOG PSDB; CREATE SCHEMA PSDB.PUBS; CREATE TABLE psdb.pubs.
The Shipping Database SET SCHEMA PSDB.
B Examples To provide notification of changes to the database, SQL/MX integrates transactional queuing and publish/subscribe into its database infrastructure. This appendix presents typical scenarios that use transactional queuing and publish/subscribe services. The examples presented in Section 2, Major Queuing Features and in Section 3, Embedded SQL Examples use the tables specified in the scenarios later in this appendix and in Appendix A, The Shipping Database.
Scenario 1. The Quotes Channel Examples Scenario 4. Fetching Contract Data is an example of a stream that is joined with another table to provide additional information. This scenario illustrates using the value of the contract number in the invoice stream as the condition for a left join operation with the contracts table.
Subscribing to Quotes Examples Suppose that the quotes table is defined as: CREATE TABLE pubs.quotes (symbol CHAR(5), price INT); For this table, the SYSKEY is the clustering key; there is no user-defined clustering key. Subscribing to Quotes An application can subscribe to stock quotes by issuing a SELECT statement that uses the stream access mode: SELECT * FROM STREAM(quotes); The stream access mode causes all available rows to be retrieved by doing a regular scan of the table.
Subscribing to Quotes Examples EXEC SQL FETCH get_quotes INTO :lastsyskey, :symbol, :price; In this example, the host variable named lastkey is maintained and saved by the application after each fetch. It is used as a starting point whenever the cursor is reopened. This technique allows a subscriber to see rows that were inserted while the subscribing application was disconnected. It takes advantage of the fact that SYSKEY values are always assigned in increasing order.
Scenario 2. The Invoices Queue Examples Scenario 2. The Invoices Queue Invoices are submitted to request payment for a service. In this scenario, an invoice must be processed exactly once and therefore should be received by only one server. The Invoices Table Suppose the invoices table is defined as: CREATE TABLE pubs.invoices (contractnbr INT, amount INT, priority INT); The invoices table does not have a user-defined clustering key.
Scenario 3. Invoice Processing by Priority Examples transaction but rather wait until other transactions have processed and committed the invoice. This strategy ensures FIFO behavior but limits concurrent processing.
Dequeueing Invoices by Priority Examples After the first regular scan of the table has been completed, an application might receive invoices out of order—that is, with a higher priority. Invoices might come back out of order because: • • The row was initially skipped due to SKIP CONFLICT ACCESS (that is, it was locked during the regular scan on the table and then unlocked later) A row did not exist in the table when the regular scan was performed and was inserted into the table later.
Scenario 4. Fetching Contract Data Examples Normally, in embedded SQL, a SELECT INTO statement is restricted to retrieving a single row (or, in the case of a rowset, the number of rows that fit into the rowset). Otherwise, if more than one row (or more than the number of rows that fit into a rowset) qualifies for retrieval, SQL/MX returns an error stating that a row subquery or SELECT INTO statement cannot return more than one row.
Joining Invoices With Contracts Examples Joining Invoices With Contracts The application can retrieve contract data along with data from a particular invoice by issuing this SELECT statement: SELECT invoices.amount, contracts.amount, invoices.contractnbr FROM (DELETE FROM STREAM(invoices)) as invoices LEFT JOIN contracts ON invoices.contractnbr = contracts.contractnbr; For this example, the join uses the primary key of the contracts table to match invoices to their respective contract.
The Inbox Table Examples The sample database for this scenario is provided in Appendix A, The Shipping Database. The Inbox Table Suppose that the inbox table is defined as: CREATE TABLE psdb.pubs.inbox (destination INT NOT NULL, origin INT NOT NULL, label INT, priority INT, PRIMARY KEY (destination, origin) ) PARTITION (ADD FIRST KEY 230 LOCATION $DATA05, ADD FIRST KEY 260 LOCATION $DATA06 ); The destination column identifies the physical destination. The origin column identifies the physical scanner.
Examples Using a Holdable Cursor to Efficiently Increase Concurrency Dequeueing Parcel Notifications From a Single Partition The router can receive and remove entries in the inbox table by partition by the scanner by issuing this SELECT statement for a cursor specification: /* Declare cursor */ EXEC SQL DECLARE get_arrivals CURSOR WITH HOLD FOR SELECT * FROM (DELETE FROM STREAM(inbox) WHERE destination BETWEEN :start AND :end FOR SKIP CONFLICT ACCESS)) AS inbox ; ... start = 229; end = 260; ...
Using a Holdable Cursor to Efficiently Increase Concurrency Examples ...
Index A E AFTER LAST ROW clause 2-8 Application logic checkpointing 2-18 ATTEMPT_ASYNCHRONOUS_ACCESS default 2-2 Audited tables, support for 2-1 Embedded DELETE cursor behavior B-7 example of 2-3, 3-3, B-5 join with primary key B-9 ordered stream B-6 performance considerations for 4-3 READ COMMITTED access B-5 referential integrity and 2-4 SELECT INTO use B-7 SKIP CONFLICT access B-5 Embedded UPDATE example of 2-6, 3-4 performance considerations for 4-2 referential integrity and 2-6 SELECT INTO use B-8 E
F Index Examples (continued) shipping application, scenario description for B-2 shipping database for A-1 simple channel, scenario description for B-1 SQL/MP and SQL/MX tables 1-5 Extensions 2-1 F Features 1-5 H Holdable cursors 2-10, 2-11 J Java applications 2-12, 3-1 Joining queues, example of B-2 Joins 2-15, 3-8 L Limits for stream access 2-16 M MATERIALIZE attribute description of 3-8 example of 3-9, B-9 MATERIALIZE operator 2-15, 4-4 O Ordered streams description of 2-14 example of 2-15, B-1 se
S Index S Scenarios invoices queue B-5/B-6 joining queues B-8/B-9 ordered stream B-6/B-8 quotes channel B-2/B-4 shipping application B-9/B-12 SELECT INTO statement, example of B-7 SELECTs, destructive 1-5 Semi-queuing, description of 1-4 SET ON ROLLBACK clause description of 2-1, 2-9 example of 2-9, 3-6 Shipping database A-1 SKIP CONFLICT access description of 2-7 example of 3-5, B-5 mode 1-5 SQL/MP and SQL/MX table support 1-5 Stream access description of 1-3, 2-2, B-1, B-3 example of 2-2, 3-2, B-3 inser
W Index HP NonStop SQL/MX Queuing and Publish/Subscribe Services —523734-002 Index -4
