Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Korn Shell Coprocess Performance Question

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Korn Shell Coprocess Performance Question
# 8  
Old 12-14-2005
Just to update you on this problem. I did implement the suggestion of listening for a specific string that indicates the completion of a SQL command. It did improve the runtime of the job, but only by about 5%. However, I do like the concept of having the script work this way. I also set the SQLPlus prompt to the null string, again to simplify parsing of the output. I changed the "suicidal read" poll interval to 60 seconds (actually it is specifiable as a parameter now, rather than hard-coded). Performance improvement was marginal.

I'm open to other suggestions at this point.....
# 9  
Old 12-14-2005
Quote:
Originally Posted by Mark Puddephat
I did implement the suggestion of listening for a specific string that indicates the completion of a SQL command. It did improve the runtime of the job, but only by about 5%.
I asked in my first response how many rows does your loop process. If there are thousands, you may want to spool the output to a file and set TERMOUT to off. This will limit the I/O bottleneck to sqlplus and not your loop. Following your co-processor read loop, implement another loop if necessary to process your results. Thousands of records through the co-processor read was just too slow for me. I used a REFCURSOR from the PL/SQL stored procedure and sqlplus PRINT.

Here is an example:
Code:
    # Prepare PL/SQL Block and create a SQL script
    print "
        SET VERIFY OFF FEEDBACK OFF HEADING OFF
        SET LINESIZE 3000 PAGESIZE 0
        SET TRIMSPOOL ON SPACE 0

        VARIABLE v_record_cnt      VARCHAR2(50)
        VARIABLE v_header_results  REFCURSOR
        VARIABLE v_detail_results  REFCURSOR
        VARIABLE v_trailer_results REFCURSOR
        BEGIN
          stored_procedure(
             -- in parameters
             ...
             -- out parameters
            ,:v_record_cnt
            ,:v_header_results
            ,:v_detail_results
            ,:v_trailer_results
          );
          :v_record_cnt := 'Record_Count:' || :v_record_cnt;
        END;
        /
        PRINT v_header_results
        PRINT v_detail_results
        PRINT v_trailer_results
" > ${TEMP_SQL_SCRIPT}

    # Launch the SQL file
    # Print the stored procedure output variables
    # Reset the SQL parameters
    # Print marker message to keep the shell script from freezing up
    print -p "
        SET TERMOUT OFF
        SPOOL ${TEMP_EXTRACT_FILE}
        @${TEMP_SQL_SCRIPT}
        SPOOL OFF
        PRINT v_record_cnt
        SET TERMOUT ON
        SET VERIFY ON FEEDBACK ON HEADING ON TRIMSPOOL OFF
        PROMPT SQL-COMPLETE
" 
... now you can process your results file ${TEMP_EXTRACT_FILE} ...

Quote:
Originally Posted by Mark Puddephat
I also set the SQLPlus prompt to the null string, again to simplify parsing of the output.
That's pretty swift, I never though of setting it to NULL. However, why is it necessary if you "SET FEEDBACK OFF VERIFY OFF ECHO OFF"?

Quote:
Originally Posted by Mark Puddephat
I changed the "suicidal read" poll interval to 60 seconds (actually it is specifiable as a parameter now, rather than hard-coded). Performance improvement was marginal.
What happens if your SQL takes 65 seconds to return data? I believe the suicidal read will terminate your job prematurely.
Last edited by tmarikle; 12-14-2005 at 06:31 PM.. Reason: PRINT vs PROMPT sqlplus syntax error
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

SQL/Plus in a coprocess example. Also saves query results into shell variables

While assisting a forum member, I recommended running SQL/Plus in a coprocess (to make database connections and run a test script) for the duration of his script rather than starting/stopping it once for every row in a file he was processing. I recalled I made a coprocess example for folks at... (2 Replies)
Discussion started by: gary_w
2 Replies

2. Shell Programming and Scripting

korn shell remove files question

how do you show each filename in a giving directory and delete the specific file in korn script i was thinking using ls rm ? but i cant make it work (0 Replies)
Discussion started by: babuda0059
0 Replies

3. Shell Programming and Scripting

Question about a simple Korn script

Hi to everybody! I want to write a simple script in ksh that decrypts and encrypts using the DES algorithm. There is no builtin function in UNIX : i have found only a function in openssl but i don't understand how to use it. The script must accept in input the plaitext and the DESKEY in... (2 Replies)
Discussion started by: kazikamuntu
2 Replies

4. AIX

AIX 4.2 Korn shell and grep question

Ho do I find out the verion of the Kron shell on my client`s system ? There is no one to ask. They are not knowledged enough (hard to believe but yes). Also, on that AIX 4.2, I am trying to figure out how to do a grep using a search patter like below but does not seam to work. The '*' do... (11 Replies)
Discussion started by: Browser_ice
11 Replies

5. Shell Programming and Scripting

Korn shell and awk question

I am modifying a Korn shell script in using the Exceed (Solaris 10 environment). My task is to read in a .txt file with dates arranged like this (01-Sep-2006). I am to read each line and take the dates, compare them to a benchmark date and depending on if it is older than the date or the date and... (6 Replies)
Discussion started by: mastachef
6 Replies

6. Shell Programming and Scripting

korn shell question

Hi all, I am trying to tweak my ksh , i am running V: Version M-11/16/88i I have my Backspace and up/down arrows working using the following code in my ~/.profile file. set -o emacs alias __A=$(print '\020' ) alias __B=$(print '\016' ) alias __C=$(print '\006' ) alias __D=$(print... (4 Replies)
Discussion started by: mich_elle
4 Replies

7. Shell Programming and Scripting

Korn Shell Loop question

I'm needing help with assigning variables inside a while loop of ksh script. I have an input text file and ksh script below and I'm trying to create a script which will read the input file line by line, assign first and second word to variables and process the variables according to the contents. ... (4 Replies)
Discussion started by: stevefox
4 Replies

8. Shell Programming and Scripting

AWK question in the KORN shell

Hi, I have two files with the following content: gmrd.txt 235649;03;2563;598 291802;00;2563;598 314634;00;235649;598 235649;03;2563;598 393692;00;2563;598 411805;00;2563;598 411805;00;2563;598 235649;03;2563;598 414037;00;2563;598 575200;00;2563;598 70710;00;2563;598... (11 Replies)
Discussion started by: penfold
11 Replies

9. Shell Programming and Scripting

Question about Korn Shell

In Korn Shell, can you use "go to" statements? Would you then put paragraph names with a colon? For example, would you specify "goto para1" and then have the paragraph with the label para1:? I am getting an error message when Idid this. I have my paragraph name 'clsbooks:' and I get... (13 Replies)
Discussion started by: Latha Nair
13 Replies

10. Shell Programming and Scripting

Question variables Korn

I'm using the following command to test for certain characters in a script echo "${1}" | grep '\$' if (( ${?} == 0 )) then testing this script on the command line I have ksh -x script1.sh "xxxx$xxxx" this works fine but when I want to use ksh -x script1.sh "xxxx $xxx" the... (1 Reply)
Discussion started by: frank
1 Replies
Login or Register to Ask a Question