Rainer Ross RainerRoss

🏠

Working from home

forstie / Publishing file contents using JSON and SQL.sql

Created June 15, 2019 05:55

Publishing file contents using JSON and SQL


	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,

forstie / remove noise from text strings using TRANSLATE.sql

Created June 15, 2019 05:54

remove noise from text strings using TRANSLATE

---
--- 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',
-      ' ',

forstie / Authority Collection.sql

Created June 15, 2019 05:51 — forked from phpdave/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

	--Authority Collection - https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzarl/rzarlautcolstart.htm

	--https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/cl/strautcol.htm
	STRAUTCOL USRPRF(USER1) LIBINF(ALL) OBJ(ALL) OBJTYPE(*ALL) OMITLIB((MYLIB1))

	--https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/cl/endautcol.htm
	ENDAUTCOL USER(USER1)

	--View Data collected
	SELECT * FROM QSYS2.AUTHORITY_COLLECTION