CODEWITHSUNDEEP
stored-proceduresparallel-processingnetezzanzsql
user3561384
user3561384

Reputation: 19

Netezza stored procedure run

I have 12 stored procedures in netezza. I call these procedures using nzsql command from a shell script. I want to run these procedure in parallel to increase throughput. How can I do this?

Upvotes: 0

Views: 1817

Answers (2)

John Flecher
John Flecher

Reputation: 5

To elaborate on @Jeremy Fortune's answer, There are three scenarios in which the system aborts a transaction to preserve serializability:

  • An update or delete statement is running concurrently with another update or delete statement on the same table.
  • Two concurrent transactions that each perform a SELECT FROM and an INSERT INTO the same table. This could occur as a self-inserting statement or multiple statements in any order. Note that up to 31 concurrent inserts into the same table are supported, provided that no more than one of these also selects from the same table.
  • Two concurrent transactions, the first of which selects from table1 and updates, inserts or deletes from table2 while the second transaction selects from table2 and updates, inserts or deletes from table1.

You can read more about it here.

However serialized transaction can be in a queue before failing and system automatically retries until it all time outs after X minutes, X is defined by serialization_queue_timeout system variable.

However this only applies to implicit transactions (transactions without BEGIN and COMMIT block), and most of store procedure transactions are explicit transactions (it's also advantage of using store procedure, everything gets rolled back if something fails, unless you have used AUTOCOMMIT ON option placed somewhere inside the store procedure), which won't let you take advantage of the serialization queue.

Upvotes: 0

Jeremy Fortune
Jeremy Fortune

Reputation: 2499

Serializable

If the stored procedures do not affect the same tables, then you can just fork the calls from bash:

nzsql -Atc "call sp1();" &
nzsql -Atc "call sp2();" &
nzsql -Atc "call sp3();" &
...
wait

See other answers about forking.

Not Serializable

If the stored procedures affect the same tables, you'll need to set serializability off in the connection or transaction that is affected. I haven't done this in a stored procedure (and you may not be able to), but this should work:

nzsql -Atc "set serializable = false; call sp1();" &
nzsql -Atc "set serializable = false; call sp2();" &
nzsql -Atc "set serializable = false; call sp3();" &
...
wait

See the docs for more information on the serializable isolation level. You'll be responsible for making sure that the data the stored procedures are modifying do not collide in some fashion, as you'll get dirty reads.

Upvotes: 1

Related Questions