SQL ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÑ ´ÙÀ½ CREATE PROCEDURE¹® ½ÇÇà¿¡ ¼³¸íµÈ ´ë·Î CREATE PROCEDURE¹®À» ¹ßÇàÇØ¾ß ÇÕ´Ï´Ù. ƯÁ¤ÇÑ °æ¿ì DB2´Â CREATE PROCEDURE¹®ÀÇ ÀÀ´äÀ¸·Î ¿À·ù¸¦ ¸®ÅÏÇÕ´Ï´Ù. ¿À·ù¸¦ Á¤Á¤ÇÏ´Â °Í¿¡ ´ëÇÑ ¼³¸í°ú Á¦¾ÈÀ» Æ÷ÇÔÇÏ¿© DB2¿¡¼ ¸®ÅÏµÈ ¿À·ù¿¡ ´ëÇØ ´õ ¸¹Àº Á¤º¸¸¦ °Ë»öÇÏ·Á¸é CLP¿¡¼ ´ÙÀ½ ¸í·ÉÀ» ¹ßÇàÇϽʽÿÀ.
db2 "? error-code"
¿©±â¼ error-code´Â ¿À·ù¿¡ ÀÇÇØ ¸®ÅÏµÈ SQLCODE ¶Ç´Â SQLSTATE¸¦ ³ªÅ¸³À´Ï´Ù. ¿¹¸¦ µé¾î, CREATE PROCEDURE¹®ÀÌ SQLCODE "SQL0469N"("¸Å°³º¯¼ö ¸ðµå°¡ À¯È¿ÇÏÁö ¾ÊÀ½")¿Í ÇÔ²² ¿À·ù¸¦ ¸®ÅÏÇÏ¸é ´ÙÀ½ ¸í·ÉÀ» ¹ßÇàÇϽʽÿÀ.
db2 "? SQL0469"
DB2´Â ´ÙÀ½ ¸Þ½ÃÁö¸¦ ¸®ÅÏÇÕ´Ï´Ù.
Explanation: One of the following errors occurred: o a parameter in an SQL procedure is declared as OUT and is used as input in the procedure body o a parameter in an SQL procedure is declared as IN and is modified in the procedure body. User Response: Change the attribute of the parameter to INOUT, or change the use of the parameter within the procedure.
ÀÏ´Ü ¸Þ½ÃÁö¸¦ Ç¥½ÃÇϸé "»ç¿ëÀÚ Á¶Ä¡" Àý¿¡ ÀÖ´Â Á¦¾È¿¡ µû¶ó SQL ÇÁ·Î½Ãµà¾î¸¦ ¼öÁ¤ÇϽʽÿÀ.
SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ CREATE PROCEDURE¹®À» ¹ßÇàÇϸé DB2´Â SQL ÇÁ·Î½Ãµà¾î º»¹®ÀÇ ±¸¹®À» ½ÂÀÎÇÏ¿© »çÀü ó¸® ÄÄÆÄÀÏÀ̳ª ÄÄÆÄÀÏ ´Ü°è¿¡¼ SQL ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÌ·± »óȲ¿¡¼ DB2´Â º¸Åë ¿À·ù ¸Þ½ÃÁö°¡ µé¾î ÀÖ´Â ·Î±× ÆÄÀÏÀ» ÀÛ¼ºÇÕ´Ï´Ù. ÀÌ ·Î±× ÆÄÀϰú ±âŸ Áß´Ü ÆÄÀÏ¿¡ ´ëÇØ¼´Â Áß°£ ÆÄÀÏÀ» »ç¿ëÇÑ SQL ÇÁ·Î½Ãµà¾î µð¹ö±ë¿¡ ¼³¸íµÇ¾î ÀÖ½À´Ï´Ù.
SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ DB2¿Í C ÄÄÆÄÀÏ·¯¿¡¼ »ý¼ºµÈ ¿À·ù ¸Þ½ÃÁö¸¦ °Ë»öÇÏ·Á¸é µ¥ÀÌÅͺ£À̽º ¼¹ö¿¡¼ ´ÙÀ½ µð·ºÅ丮¿¡ ¸Þ½ÃÁö ·Î±× ÆÄÀÏÀ» Ç¥½ÃÇϽʽÿÀ.
¿©±â¼ $DB2PATH´Â ÀνºÅϽº µð·ºÅ丮ÀÇ À§Ä¡¸¦ ³ªÅ¸³»°í $DATABASE´Â µ¥ÀÌÅͺ£À̽º À̸§À» ³ªÅ¸³»¸ç $SCHEMA´Â SQL ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÒ ¶§ »ç¿ëµÇ´Â ½ºÅ°¸¶ À̸§À» ³ªÅ¸³À´Ï´Ù.
¿©±â¼ %DB2PATH%´Â ÀνºÅϽº µð·ºÅ丮ÀÇ À§Ä¡¸¦ ³ªÅ¸³»°í %DB%´Â µ¥ÀÌÅͺ£À̽º À̸§À» ³ªÅ¸³»¸ç %SCHEMA%´Â SQL ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÒ ¶§ »ç¿ëµÇ´Â ½ºÅ°¸¶ À̸§À» ³ªÅ¸³À´Ï´Ù.
ÀÀ¿ëÇÁ·Î±×·¥¿¡¼ CALL¹®À» ¹ßÇàÇÏ¿© ´ÙÀ½ ±¸¹®À» ÅëÇØ db2udp!get_error_messages »ùÇà ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÒ ¼öµµ ÀÖ½À´Ï´Ù.
CALL db2udp!get_error_messages(schema-name, file-name, message-text)
¿©±â¼ schema-nameÀº SQL ÇÁ·Î½Ãµà¾îÀÇ ½ºÅ°¸¶¸¦ ³ªÅ¸³»´Â ÀÔ·Â ¸Å°³º¯¼öÀ̰í file-nameÀº SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ »ý¼ºµÈ ÆÄÀÏ À̸§À̸ç message-text´Â ¸Þ½ÃÁö ·Î±× ÆÄÀÏ·Î ¸Þ½ÃÁö ÅØ½ºÆ®¸¦ ¸®ÅÏÇÏ´Â Ãâ·Â ¸Å°³º¯¼öÀÔ´Ï´Ù.
¿¹¸¦ µé¾î, ´ÙÀ½ Java ÀÀ¿ëÇÁ·Î±×·¥À» »ç¿ëÇÏ¿© SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ ¿À·ù ¸Þ½ÃÁö¸¦ Ç¥½ÃÇÒ ¼ö ÀÖ½À´Ï´Ù.
public static String getErrorMessages(Connection con, String procschema, String filename) throws Exception { String filecontents = null; // prepare the CALL statement CallableStatement stmt = null; try { String sql = "Call db2udp!get_error_messages(?, ?, ?) "; stmt = con.prepareCall (sql); // set all parameters (input and output) stmt.registerOutParameter( 3, java.sql.Types.LONGVARCHAR ); stmt.setString( 1, procschema ); stmt.setString( 2, filename ); // call the stored procedure boolean isrs = stmt.execute(); filecontents = stmt.getString(3); System.out.println("SQL Procedure - getErrorMessages " + filecontents); return filecontents; } catch (Exception e) { throw e; } finally { if (stmt != null) stmt.close(); } }
´ÙÀ½ C ÀÀ¿ëÇÁ·Î±×·¥À» »ç¿ëÇÏ¿© SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ ¿À·ù ¸Þ½ÃÁö¸¦ Ç¥½ÃÇÒ ¼ö ÀÖ½À´Ï´Ù.
int getErrors(char inputSchema[9], char inputFilename[9], char outputFilecontents[32000]) { EXEC SQL BEGIN DECLARE SECTION; char procschema[100] = ""; char filename[100] = ""; char filecontents[32000] = ""; EXEC SQL END DECLARE SECTION; strcpy (procschema, inputSchema); strcpy (filename, inputFilename); EXEC SQL CALL "db2udp!get_error_messages" (:procschema, :filename, :filecontents); if ( sqlca.sqlcode != 0 ) { printf("Call failed. Code: %d\n", sqlca.sqlcode); return 1; } else { printf("\nSQL Procedure - getErrors:\n%s\n", filecontents); } strcpy (outputFilecontents, filecontents); return 0; }
ÁÖ: | DB2¿¡¼ ÀÛ¼ºÇÏÁö ¸øÇÑ SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ ¿À·ù ¸Þ½ÃÁö¸¦ Ç¥½ÃÇϱâ Àü¿¡
ÇÁ·Î½Ãµà¾î À̸§°ú SQL ÇÁ·Î½Ãµà¾îÀÇ »ý¼ºµÈ ÆÄÀÏ À̸§À» ¾Ë°í ÀÖ¾î¾ß ÇÕ´Ï´Ù.
ÇÁ·Î½Ãµà¾î ½ºÅ°¸¶ À̸§ÀÌ CREATE PROCEDURE¹®ÀÇ ÀϺηΠ¹ßÇàµÇÁö ¾ÊÀ¸¸é DB2´Â
CURRENT SCHEMA Ư¼ö ·¹Áö½ºÅÍÀÇ °ªÀ» »ç¿ëÇÕ´Ï´Ù. CURRENT SCHEMA Ư¼ö ·¹Áö½ºÅÍÀÇ
°ªÀ» Ç¥½ÃÇÏ·Á¸é CLP¿¡¼ ´ÙÀ½ ¸í·É¹®À» ¹ßÇàÇϽʽÿÀ.
VALUES CURRENT SCHEMA |
SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ CREATE PROCEDURE¹®À» ¹ßÇàÇϰí DB2°¡ SQL ÇÁ·Î½Ãµà¾î º»¹®ÀÇ ±¸¹®À» ½ÂÀÎÇϸé DB2´Â ¿©·¯ °³ÀÇ Áß°£ ÆÄÀÏÀ» »ç¿ëÇÏ¿© SQL ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÕ´Ï´Ù. DB2°¡ ¼º°øÀûÀ¸·Î SQL ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÑ ´ÙÀ½ ÀÌ´Â º¸Åë Áß°£ ÆÄÀÏÀ» Á¦°ÅÇÏ¿© ½Ã½ºÅÛ ÀÚ¿øÀ» Àý¾àÇÕ´Ï´Ù. DB2°¡ CREATE PROCEDURE ±¸¹®À» ½ÂÀÎÇÏÁö¸¸ SQL ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÏÁö ¸øÇϸé ÀÌ´Â CREATE PROCEDURE ÇÁ·Î¼¼½ºÀÇ »çÀü ó¸® ÄÄÆÄÀÏ, ¹ÙÀÎµå ±×¸®°í ÄÄÆÄÀÏ ´Ü°è¸¦ ÃßÀûÇÏ´Â ·Î±× ÆÄÀÏÀ» À¯ÁöÇÕ´Ï´Ù.
UNIX ½Ã½ºÅÛ¿¡¼ DB2´Â instance/function/routine/sqlproc/dbAlias/schemaÀÇ ±âº» µð·ºÅ丮¸¦ »ç¿ëÇÏ¿© Áß°£ ÆÄÀÏÀ» º¸°üÇÕ´Ï´Ù. ¿©±â¼ instance´Â DB2 ÀνºÅϽºÀÇ °æ·Î¸¦ ³ªÅ¸³»°í dbAlias´Â µ¥ÀÌÅͺ£À̽º º°¸íÀ» ³ªÅ¸³»¸ç schema´Â CREATE PROCEDURE¹®ÀÌ ¹ßÇàµÈ ½ºÅ°¸¶¸¦ ³ªÅ¸³À´Ï´Ù.
OS/2¿Í Windows 32-ºñÆ® ¿î¿µ üÁ¦¿¡¼ DB2´Â instance\function\routine\sqlproc\dbAlias\schemaÀÇ ±âº» µð·ºÅ丮¸¦ »ç¿ëÇÏ¿© Áß°£ ÆÄÀÏÀ» º¸°üÇÕ´Ï´Ù. ¿©±â¼ instance´Â DB2 ÀνºÅϽº¸¦ ³ªÅ¸³»°í dbAlias´Â µ¥ÀÌÅͺ£À̽º º°¸íÀ» ³ªÅ¸³»¸ç schema´Â CREATE PROCEDURE¹®ÀÌ ¹ßÇàµÈ ½ºÅ°¸¶¸¦ ³ªÅ¸³À´Ï´Ù.
SQL ÇÁ·Î½Ãµà¾î°¡ ¼º°øÀûÀ¸·Î ÀÛ¼ºµÇ¾úÁö¸¸ CALL¹®¿¡¼ ¿¹»ó °á°ú¸¦ ¸®ÅÏÇÏÁö ¾ÊÀ¸¸é Áß°£ ÆÄÀÏÀ» °Ë»çÇÒ ¼ö ÀÖ½À´Ï´Ù. DB2°¡ Áß°£ ÆÄÀÏÀ» Á¦°ÅÇÏÁö ¸øÇÏ°Ô ¸·À¸·Á¸é ´ÙÀ½ ¸í·ÉÀ» »ç¿ëÇÏ¿© DB2_SQLROUTINE_KEEP_FILES DB2 ·¹Áö½ºÆ®¸® º¯¼ö¸¦ "¿¹"·Î ¼³Á¤ÇϽʽÿÀ.
db2set DB2_SQLROUTINE_KEEP_FILES="yes"
DB2°¡ ·¹Áö½ºÆ®¸® º¯¼öÀÇ »õ °ªÀ» »ç¿ëÇϱâ Àü¿¡ µ¥ÀÌÅͺ£À̽º¸¦ ´Ù½Ã ½ÃÀÛÇØ¾ß ÇÕ´Ï´Ù.