Utilizzo delle stored procedure in DataStage

Connettori supportati

I seguenti connettori supportano le procedure memorizzate. Le procedure memorizzate possono essere eseguite su connettori con link di input, link di output o entrambi.

• Amazon RDS for PostgreSQL
• IBM Cloud® Databases for PostgreSQL
• IBM Db2®
• IBM® Db2 Big SQL
• IBM Db2 on Cloud
• IBM Db2 for i
• IBM Db2 Warehouse
• IBM Db2 for z/OS®
• Google BigQuery
• Microsoft Azure SQL Database
• Microsoft SQL Server
• Microsoft Azure Synapse Analytics
• Oracle
• PostgreSQL
• SAP ASE
• Snowflake
• Teradata

Procedure memorizzate in uno stage di origine

Selezionare Aggiungi valore di ritorno procedura allo schema per aggiungere il codice di ritorno della procedura e il messaggio allo schema come colonne ProcCode e ProcMess. La prima colonna sul collegamento di output contiene il valore di codice. La seconda colonna contiene il valore del messaggio. Se si seleziona anche Inoltra dati riga, le colonne di input vengono aggiunte al link di output dopo le colonne ProcCode e ProcMess .

Procedure memorizzate in uno stage di destinazione

Selezionare Esegui procedura per ogni riga per eseguire la procedura per ogni riga dei dati di input. Se non si seleziona Esegui procedura per ogni riga, la procedura verrà eseguita una sola volta, per la prima riga. Per impostazione predefinita, è selezionata l'opzione Esegui procedura per ciascuna riga . Questa procedura non è supportata dal connettore Snowflake.

Sintassi

CALL <procedure_name>(@<column_name>, ?)

? = CALL <procedure_name>('@<string_column_name>', ?)

Nella scheda Fase, in Proprietà generali dell'origine se si sceglie Metodo di lettura > Dichiarazione di chiamata della procedura e si seleziona Aggiungi il valore di ritorno della procedura allo schema, due colonne (ProcCode e ProcMess) vengono aggiunte all'output come prime colonne dello schema di output.

Se la stored procedure viene chiamata in uno stage con solo collegamenti di uscita, ? indica sempre un parametro di uscita. Se la stored procedure viene chiamata in uno stage con solo collegamenti di input, ? indica sempre un parametro di input.

CALL <procedure_name>()
CALL <procedure_name>(?, ?)
CALL <procedure_name>(4,?)

Una stored procedure per il connettore PostgreSQL non può restituire più righe nei parametri di output. Per ottenere più righe, utilizzare invece la tabella delle funzioni definite dall'utente.

Valori parametro

Nella sezione Valori di parametri della scheda Stage, utilizzare specifiche di parametro per associare i parametri di input e output della procedura memorizzata alle colonne di input e output. Determina le colonne in cui vengono restituiti i dati. I nomi dei parametri nella scheda Stage devono corrispondere ai nomi dei parametri della stored procedure. Ogni nome parametro può essere associato a una singola colonna. Se non viene fornita una mappatura, i dati di output per ciascuna colonna vengono restituiti nell'ordine in cui le colonne sono specificate. Un connettore con un link di input avrà parametri di input, un connettore con un link di output avrà parametri di output e un connettore con entrambi i link di input e di output avrà entrambi.

Ogni parametro deve essere mappato sulla colonna di input. Ad esempio, P1 dovrebbe essere mappato su COLUMN_1 e P2 su COLUMN_2. La mappatura dei parametri non sostituisce la definizione della colonna. Tuttavia, è possibile scartare il valore da un collegamento di input e utilizzare il valore nella colonna Marker / Literal.

Parametri di Inout

Per il connettore Microsoft SQL Server è possibile utilizzare un tipo di parametro inout per le stored procedure. Il parametro inout agisce sia come parametro di ingresso che di uscita. Una procedura riceve un valore per il parametro, lo elabora e restituisce un valore modificato attraverso lo stesso parametro. È possibile utilizzare i parametri di inout solo quando il connettore è configurato in modalità di trasformazione.

È possibile passare il valore di ingresso del parametro inout solo attraverso il collegamento o da una mappa di parametri nella sezione Valori dei parametri. I valori di input per il parametro inout non possono essere fissati nella vostra dichiarazione. Ad esempio, ?=(CALL) test (1,?,?) è un'affermazione errata, mentre ?=CALL test (?,?,?) è corretta.

Parametri facoltativi

È possibile utilizzare parametri opzionali solo per le stored procedure. Le funzioni definite dall'utente non supportano parametri opzionali. Una stored procedure può avere uno o più parametri opzionali. Se si desidera omettere i parametri opzionali durante la chiamata di una stored procedure, utilizzare l 'istruzione Call procedure. Se una stored procedure ha uno o più parametri opzionali, tutti i parametri opzionali devono essere definiti dopo i parametri di output. I parametri opzionali con il valore " Null devono essere raggruppati alla fine.

Esempio: Una stored procedure " MYSCHEMA.MYSP ha due parametri di input obbligatoriinparam1, inparam2), due input opzionalioptinparam3, optinparam4) e due parametri di output. Si vedano i seguenti esempi di utilizzo valido e non valido dell'istruzione Call procedure:

VALIDO:

CALL MYSCHEMA.MYSP(inparam1, inparam2, ?, ?, optinparam3, optinparam4)

CALL MYSCHEMA.MYSP(inparam1, inparam2, ?, ?, optinparam3)

CALL MYSCHEMA.MYSP(in1, in2, ?, ?)

CALL MYSCHEMA.MYSP(inparam1, inparam2, optinparam3, optinparam4, ?, ?)

CALL MYSCHEMA.MYSP(inparam1, inparam2, ?, ?, , optinparam4)

Timeout della query

Per query e procedure memorizzate di lunga durata, impostare la proprietà Timeout query per limitare il numero di secondi per cui viene eseguita una procedura. Il valore predefinito è 300 secondi. Se si imposta un valore superiore a 600 secondi, è necessario anche modificare la variabile di ambiente $OSH_JOB_START_TIMEOUT e impostare un valore superiore al Timeout query. Questa proprietà è disponibile su Microsoft SQL Server, Microsoft Azure SQL Database, IBM Db2, IBM Db2 Big SQL, IBM Db2 on Cloud, IBM Db2 for i, IBM Db2 Warehousee IBM Db2 for z/OS.

Funzioni definite dall'utente

Quando si seleziona Richiama procedura o Richiama istruzione procedura , è possibile selezionare Funzione definita dall'utente per specificare una funzione definita dall'utente da eseguire. Una funzione definita dall'utente può utilizzare parametri di immissione e restituire un singolo valore (per funzioni scalari) o una serie di valori (per funzioni di tabella). Questa proprietà è disponibile su Amazon RDS for PostgreSQL, 'Db2, IBM Cloud Databases for PostgreSQL, 'IBM Db2 Big SQL, IBM 'Db2 on Cloud, IBM 'Db2 for i, IBM 'Db2 Warehouse, IBM 'Db2 for z/OS, PostgreSQL, 'Snowflake, e Teradata. Nel 'Db2 for z/OS di IBM, le funzioni definite dall'utente devono essere richiamate come parte di un'istruzione SELECT. In Teradata sono disponibili anche funzioni di tipo macro.

Le funzioni definite dall'utente supportano sia la modalità di origine che quella di trasformazione. Per la modalità Sorgente, i dati vengono letti dal database. Per la modalità Transform, i dati vengono letti, trasformati e scritti sul nodo di destinazione.