daveh42
daveh42
/ Publishing file contents using JSON and SQL.sql
Created
March 4, 2022 15:58
— forked from forstie/Publishing file contents using JSON and SQL.sql
Publishing file contents using JSON and SQL
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| stop; | |
| -- Publish the data within a table using SQL | |
| with json_rows (j) as ( | |
| select json_object( | |
| key 'EMPNO' value empno, key 'FIRSTNME' value firstnme, | |
| key 'MIDINIT' value midinit, key 'LASTNAME' value lastname, | |
| key 'WORKDEPT' value workdept, key 'PHONENO' value phoneno, | |
| key 'HIREDATE' value hiredate, key 'JOB' value job, |
daveh42
/ remove noise from text strings using TRANSLATE.sql
Created
March 4, 2022 15:58
— forked from forstie/remove noise from text strings using TRANSLATE.sql
remove noise from text strings using TRANSLATE
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- Lets say I have this character data, how do I extract the numeric? | |
| -- | |
| -- Q: Library . . . . . . : SYSIBM Number of objects . : 65 | |
| -- A: Use the TRANSLATE built-in function to convert all the noise characters to blanks | |
| values bigint( | |
| translate( | |
| 'Library . . . . . . : SYSIBM Number of objects . : 65', | |
| ' ', |
daveh42
/ Authority Collection.sql
Created
March 4, 2022 15:58
— forked from forstie/Authority Collection.sql
IBM i DB2 security - Scott's examples on create mask - https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcrtmask.htm
daveh42
/ Sending an E-mail via SQL.sql
Created
March 4, 2022 15:57
— forked from forstie/Sending an E-mail via SQL.sql
Sending an E-mail via SQL
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- setup | |
| cl: STRTCPSVR SERVER(*SMTP) ; | |
| cl: ADDUSRSMTP USRPRF(SCOTTF); | |
| cl: ADDUSRSMTP USRPRF(TIMMR); | |
| -- Send SMTP E-mail Message (SNDSMTPEMM) | |
| cl:SNDSMTPEMM RCP(('forstie@us.ibm.com' *pri)) SUBJECT('hello world again') NOTE('this is a new note'); | |
| cl:SNDSMTPEMM RCP(('forstie@us.ibm.com' *pri)) SUBJECT('hello world again') NOTE('this is a new note'); | |
| select * from SQLISFUN00.may17objs; |
daveh42
/ Dates and TIMESTAMP_FORMAT
Created
March 4, 2022 15:57
— forked from forstie/Dates and TIMESTAMP_FORMAT
Formatting date data into true date and time date types
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Author: Scott Forstie | |
| -- Email: forstie@us.ibm.com | |
| create or replace variable coolstuff.decdate dec(6,0); | |
| set coolstuff.decdate = '190718'; | |
| -- July 18, 2019 (yes, really!) | |
| values timestamp_format(varchar(coolstuff.decdate), 'YYMMDD'); | |
| -- Wow | |
| -- Yowza |
daveh42
/ JSON_TABLE and survival tips for shredding JSON with SQL
Created
March 4, 2022 15:56
— forked from forstie/JSON_TABLE and survival tips for shredding JSON with SQL
This example shows how to overcome what seems to be commonplace: JSON Web Services that return an invalid JSON document.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- This fails to return data....why? | |
| SELECT cusip, issueDate, bidToCoverRatio | |
| FROM JSON_TABLE( | |
| SYSTOOLS.HTTPGETCLOB('https://www.treasurydirect.gov/TA_WS/securities/announced?format=json&type=FRN&pagesize=5', null), | |
| '$.root[*]' | |
| COLUMNS(cusip VARCHAR(10) PATH '$.cusip', | |
| issueDate Timestamp PATH '$.issueDate', | |
| bidToCoverRatio double PATH '$.bidToCoverRatio') | |
| ) AS X; | |
| stop; |
daveh42
/ dynamic VALUES INTO
Created
March 4, 2022 15:56
— forked from forstie/dynamic VALUES INTO
This example shows how to use dynamic SQL (PREPARE and EXECUTE) to implement a VALUES INTO statement.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| create procedure qgpl.values_into (out pout integer) | |
| begin | |
| declare values_into_stmt varchar(1000) ccsid 37; | |
| set values_into_stmt = 'values 1+2+3 into ?'; | |
| prepare values_into_query from values_into_stmt; | |
| execute values_into_query using pout; | |
| end; | |
| call qgpl.values_into(?); | |
| -- Note that 6 is returned... |
daveh42
/ 3-part naming for UDTFs
Created
March 4, 2022 15:54
— forked from forstie/3-part naming for UDTFs
For User Defined Table Functions (UDTFs), a trick needs to be employed. Include a where clause whose only purpose is to push the UDTF invocation to the remote database!
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- Traditional 3-part name SQL would reference a table, view, or procedure | |
| -- | |
| call otherRDB.schema.procedure123(); | |
| stop; | |
| insert into localschema.fact_table | |
| select * from otherRDB.remoteschema.fact_table; | |
| stop; |
daveh42
/ Temporalize a library.sql
Created
March 4, 2022 15:54
— forked from forstie/Temporalize a library.sql
System period temporal tables were added as a feature built into Db2 for i with IBM i 7.3. This example shows how Temporal could be established for all database files within a specific library.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- | |
| -- description: find database files and deploy Temporal over them | |
| -- note: The history table will be named <existing-table-name>_HISTORY | |
| -- note: Uncomment the LPRINTF's if you've built this procedure or have it from Db2 for i | |
| -- minvrm: V7R3M0 | |
| -- | |
| CREATE OR REPLACE PROCEDURE coolstuff.deploy_temporal(target_library varchar(10)) | |
| BEGIN |
daveh42
/ Use ACS on your IBM i to build spreadsheets.sql
Created
March 4, 2022 15:54
— forked from forstie/Use ACS on your IBM i to build spreadsheets.sql
This example simplifies a previous gist. ACS is now being shipped on your IBM i via PTFs. Subsequent PTFs will ship when major enhancements are made to ACS.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- Now that ACS is shipped on the IBM i via PTFs, we no longer need to manually move the | |
| -- acsbundle.jar onto the IBM i. Just apply the latest ACS PTFs and reference the jar | |
| -- at: /QIBM/proddata/Access/ACS/Base/acsbundle.jar | |
| -- | |
| -- Initial PTFs of ACS | |
| -- V7R4M0 SI71900 | |
| -- V7R3M0 SI71934 | |
| -- |
NewerOlder