Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2007
    Posts
    27

    Unanswered: upload csv file using sql stored procedure

    Hi guys i'm new to DB2 is any one could help me?
    Because I wanted to use DB2 IMPORT command and call an sql stored procedure to insert data.

    The reason is that i'm inserting a large numbers of rows but seems to me
    at 18,000 rows it takes a lot of minutes to insert all of it.

  2. #2
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    Hi, use sysproc.admin_cmd procedure (introduced in 8.2 )
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  3. #3
    Join Date
    Jun 2007
    Posts
    27
    Thanks for the reply...
    Can you give me a sample?

  4. #4
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    the procedure admin_cmd is described here.
    ftp://ftp.software.ibm.com/ps/produc...S/db2are90.pdf
    but check syntax for import against command reference on your version database
    example are in this pdf:
    CALL SYSPROC.AMDIN_CMD (’IMPORT FROM /home/userid/data/myfile.ixf OF IXF MESSAGES ON SERVER INSERT INTO STAFF’)
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  5. #5
    Join Date
    Jun 2007
    Posts
    27
    Thanks for the reply.

    But I wanted like for example.

    IMPORT FROM mydata.csv OF DEL CALL MYPROCEDURE(?,?,?,?)

    is this right but for me its not working at all.

  6. #6
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    examle is here :
    CALL SYSPROC.AMDIN_CMD (’IMPORT FROM /home/userid/data/myfile.ixf OF IXF MESSAGES ON SERVER INSERT INTO STAFF’)

    you need have valid import command as input string into admin_cmd procedure
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  7. #7
    Join Date
    Jun 2007
    Posts
    27
    call administrator.extract_wafer_fwfr('IMPORT FROM "D:/SLIDER_PROJECT/Data/2004F.csv" OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) MESSAGES ON SERVER INSERT INTO ADMINISTRATOR.SLDR_RAW ("COMMENT", WAFERROW, GOOD, CLASS, JOBNUM, OP, TXNTS, USERID, TOOL, PRODUCT)')

    I've made this type of call but i've an error.

    SQL0440N No authorized routine named "ADMINISTRATOR.EXTRACT_WAFER_FWFR" of
    type "PROCEDURE" having compatible arguments was found. SQLSTATE=42884

  8. #8
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    ?? please paste whole code of procedure administrator.extract_wafer_fwfr.
    thx
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  9. #9
    Join Date
    Jun 2007
    Posts
    27
    CREATE PROCEDURE ADMINISTRATOR.Extract_Wafer_Fwfr ( IN comment_in VARCHAR(30),
    IN waferrow_in CHARACTER(8),
    IN good_in INTEGER,
    IN class_in VARCHAR(30),
    IN jobnum_in INTEGER,
    IN op_in VARCHAR(30),
    IN txnts_in VARCHAR(30),
    IN userid_in VARCHAR(30),
    IN tool_in VARCHAR(30),
    IN product_in CHARACTER(6))
    SPECIFIC ADMINISTRATOR.Extract_Wafer_Fwfr

    P1: BEGIN

    DECLARE chk_val INT DEFAULT 0;
    DECLARE v_comment VARCHAR(30);
    DECLARE v_waferrow CHARACTER(8);
    DECLARE v_class VARCHAR(30);
    DECLARE v_jobnum INT DEFAULT 0;
    DECLARE v_op VARCHAR(30);
    DECLARE v_txnts VARCHAR(30);
    DECLARE v_userid VARCHAR(30);
    DECLARE v_tool VARCHAR(30);
    DECLARE v_product CHARACTER(6);

    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE sr CURSOR FOR
    SELECT comment,waferrow,class,jobnum,op,txnts,userid,tool ,product
    FROM ADMINISTRATOR.SLDR_RAW
    WHERE comment = comment_in
    AND waferrow = waferrow_in
    AND class = class_in
    --AND jobnum = jobnum_in
    AND op = op_in
    --AND userid = userid_in
    AND tool = tool_in
    AND product = product_in;
    DECLARE CONTINUE HANDLER FOR not_found
    SET chk_val = 1;

    OPEN sr;
    FETCH sr INTO v_comment,v_waferrow,v_class,v_jobnum,v_op,v_txnts ,v_userid,v_tool,v_product;

    IF chk_val = 1 THEN
    INSERT INTO sldr_raw VALUES(comment_in,waferrow_in,good_in,class_in,job num_in,op_in,txnts_in,userid_in,tool_in,product_in );
    ELSE
    UPDATE sldr_raw
    SET txnts = txnts_in,good = good_in,product = product_in,tool = tool_in,userid = userid_in
    WHERE comment = comment_in
    AND waferrow = waferrow_in
    AND class = class_in
    --AND jobnum = jobnum_in
    AND op = op_in
    --AND userid = userid_in
    AND tool = tool_in
    AND product = product_in;
    END IF;

    CLOSE sr;

    END P1


    This is my procedure to that only filters data that is duplicated.
    tnx for the time and support....

  10. #10
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    CALL SYSPROC.AMDIN_CMD (’IMPORT FROM /home/userid/data/myfile.ixf OF IXF MESSAGES ON SERVER INSERT INTO STAFF’)

    this line you must place into your procedure
    sysproc.admin_cmd is built - in procedure
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •