DB2 Z/OS : Find missing data in table
I have a list of tables and I want to find out the missing tables in db2 Z/OS.
I can think of using CTE with hardcoded values(UNION ALL) and then performing a LEFT OUTER JOIN on catalog table.
Not sure how to hardcode the values in CTE. Is there any other faster way to query the existence of tables as I have a huge list of tables that needs to be checked.
Load your huge list into some intermediate table, let's say with (CREATOR VARCHAR(128), NAME VARCHAR(128)) fields. As for hardcoded values. Try this:
select v.* from ( select 'SYSIBM', 'SYSTABLES' from sysibm.sysdummy1 union all select 'SYSIBM', 'SYSCOLUMNSS' from sysibm.sysdummy1 -- union all --select ... ) v (creator, name) where not exists ( select 1 from sysibm.systables t where t.creator=v.creator and t.name=v.name );
You can use some text processing utilities like sed to produce the strings like:
select 'SYSIBM', 'SYSTABLES' from sysibm.sysdummy1 union all
from those found in your input file:
You can do some minimal changes to the results of such a processing to construct the final statement like above. But it's not for really huge number of tables, of course...
See also questions close to this topic
How to self join with condition and add a column previous?
i have this query
SELECT "os_bulan_lalu", "os_update" FROM ( SELECT (SELECT "OUTSTANDING" FROM tbl_nominatif_hasil WHERE "BUSS_DATE"='2018-11-30' AND "COLLDET"='1') as os_bulan_lalu, (SELECT "OUTSTANDING" FROM tbl_nominatif_hasil WHERE "BUSS_DATE"='2018-12-17' AND "COLLDET"='2A') as os_update ) AS temp
but this query is error when i running and i want the result like
os_bulan_lalu | os_update 232323 | 21424 343242 | 432 23432 | 23432 234324 | 234324 234324 | 23423423
i mean os_bulan_lalu is where
BUSS_DATE"='2018-11-30' AND "COLLDET"='1', and os_update is
"BUSS_DATE"='2018-12-17' AND "COLLDET"='2A'
T-SQL - Dynamic create dates with variable
Suppose i have this table :
dateBegin DateEnd c_codeNum name 12:10:2016 12:10:2019 2 Mark 12:10:2018 12:10:2019 3 Mark
What i need is to create new dates based on the c_codeNum till the dateend.
The c_codeNum is wrote like this :
1 Annually 2 Semester 3 Quarter ... 12 Monthly
so the results i would like to be : ---> Every 6 months till 12:10:2018, then every 4 month
dateDateEnd name 12:10:2016 Mark 12:04:2017 Mark 12:10:2017 Mark 12:04:2018 Mark 12:10:2018 Mark ---the c_codeNum changed 12:02:2019 Mark 12:06:2019 Mark 12:10:2019 Mark
SQL does not recognize OFFSET
I am using sql MSMS v17.9, which is quite a late version. However when I try to use OFFSET FETCH, it does not recognize the OFFSET function and gives an error. I am using it after an Order by, but still not working... Is there another easy alternative that I could use for the same purpose?
Below is my code
`CREATE PROCEDURE [ft_omni_sales].GetNRows (@startidx int = 2, @rowcount int = 2) AS SELECT [order_receipt_row_id] ,F.[order_receipt_id] ,F.[date_id] ,F.[time_id] ,F.[article_id] ,F.[enterprise_size_id] ,F.[sales_no_of_pieces] FROM [mart].[ft_omni_sales] AS F jOIN (select distinct ty.date_id as date_id_ty ,ty.WEEK_END_DATE as WEEK_END_DATE_ty ,ty.week_desc as week_desc from exa.[DIM_DATE] as ty where ty.week_desc= (select week_desc from exa.dim_hm_date where date_id= CAST(GETDATE()-7 AS DATE)) ) as w on f.date_id=w.date_id_ty wHERE [sales_channel_id]='2' AND [fiscal_ctry_id]= '4' AND Sales_no_of_pieces > '0' Order by f.date_id, f.order_receipt_id, OFFSET @startidx ROWS FETCH Next @rowcount ROWS ONLY GO
Trying to start two instances of ibm cloud app - getting Error during WebSocket handshake: Unexpected response code: 500
I have problem with socket.io app on IBM Cloud (bluemix). Using redis and nodejs. I get the exception failed (sometimes it does the login process even when the exception is thrown, sometimes not)
How to get ' COUNT DISTINCT' over moving window
I'm working on a query to compute the distinct users of particular features of an app within a moving window. So, if there's a range from 15-20th October, I want a query to go from 8-15 Oct, 9-16 Oct etc and get the count of distinct users per feature. So for each date, it should have x rows where x is the number of features.
I have a query the following query so far:
WITH V1(edate, code, total) AS ( SELECT date, featurecode, DENSE_RANK() OVER ( PARTITION BY (featurecode ORDER BY accountid ASC) + DENSE_RANK() OVER ( PARTITION BY featurecode ORDER By accountid DESC) - 1 FROM.... GROUP BY edate, featurecode, appcode, accountid HAVING appcode='sample' AND eventdate BETWEEN '15-10-2018' And '20-10-2018' ) Select distinct date, code, total from V1 WHERE date between '2018-10-15' AND '2018-10-20'
This returns the same set of values for all the dates. Is there any way to do this efficiently?? It's a DB2 database by the way but I'm looking for insight from postgresql users too.
Present result- All the totals are being repeated. date code total 10/15/2018 appname-feature1 123 10/15/2018 appname-feature2 234 10/15/2018 appname-feature3 321 10/16/2018 appname-feature1 123 10/16/2018 appname-feature2 234 10/16/2018 appname-feature3 321 Desired result. date code total 10/15/2018 appname-feature1 123 10/15/2018 appname-feature2 234 10/15/2018 appname-feature3 321 10/16/2018 appname-feature1 212 10/16/2018 appname-feature2 577 10/16/2018 appname-feature3 2345
DB2 SQL If statement inside select and inner join
I have a simple query. I am doing a simple select (see below). It works now I want to do an inner join to another table and also do an if statement inside. I have been searching the web and cannot find what I am looking for.
I basically want to join into the item master table and if it is type 'E' do something else do something.
Can someone point me in the right direction?
EXEC SQL Declare RSCURSOR cursor for SELECT A.*, B.* FROM HPO A INNER JOIN IIML01 B on A.PPROD = B.IPROD IF B.IITYP <> 'E'; PCMT = B.IDESC; ELSE; PCMT = A.PCMT ; WHERE PORD = :PON AND PLINE = :LIN; EXEC SQL Open RSCURSOR; EXEC SQL SET RESULT SETS Cursor RSCURSOR; IF SQLSTATE = '00000'; RSL = '00'; ELSEIF SQLSTATE = '02000'; RSL = '10'; ELSE; RSL = '20'; ENDIF;
This is what I have in RPG fixed:
C PPROD CHAIN IPI100IM 99 C *IN99 IFEQ '0' C C IITYP IFNE 'E' C EVAL RS(CNT).PCMT = IDESC C ELSE C EVAL RS(CNT).PCMT = PCMT C ENDIF
Mainframe - Sort/ICETOOL for matching duplicate rows
Looking for an ICETOOL/SORT JCL only solution for this.
Let's say I have a file containing a list of cars and their colors. In the case for repeat car models, I want the output to contain only one record for the car make and model, and have all of the colors listed next to it, sorted on color, then car.
RED HONDA ACCORD BLUE TOYOTA COROLLA YELLOW NISSAN ALTIMA BLUE HONDA ACCORD GREEN HONDA ACCORD RED TOYOTA COROLLA BLACK BMW 328 BLUE BMW 328
BLACK/BLUE,BMW 328 BLUE/GREEN/RED,HONDA ACCORD BLUE/RED,TOYOTA COROLLA YELLOW,NISSAN ALTIMA
I need it to support any number of colors. Right now I have a JOINKEYS solution I wrote, but it only can handle a duplicate record. It cannot handle multiples of 3, 4, etc.
Bootstrapping Bazel build on Linux s390x - build error, no toolchain found
I'm trying to build bazel on a Linux/390 RHEL 7 system, and I believe I have all of the pre-reqs satisfied. I'm trying to bootstrap the 0.20.0 build, and get this error:
ERROR: No toolchain found for cpu 's390x'. Valid toolchains are: [ stub_armeabi-v7a: --cpu='armeabi-v7a' --compiler='compiler', local: --cpu='piii' --compiler='compiler', msys_x64_mingw: --cpu='x64_windows' --compiler='mingw-gcc', msvc_x64: --cpu='x64_windows' --compiler='msvc-cl', ]
I've looked around through the doc and the source, and it looks like s390x is a supported platform. It's listed in several BUILD files other parts in the source tree, and it's listed as a platform here:
Is there some toolchain that I need to create first before the bootstrap build will work?
How to order control m job using REXX? like Control m utility CTMAPI
I have to order few jobs in control m from different scheduling tables. this is manual task so i want to automate it using rexx. I found below in 'Order or Force under Batch, REXX or CLIST' section of 'CONTROL M USERGUIDE' EXEC CTMAPI PARM=‘ORDER variable’ I could not find syntax to call CMTAPI using rexx.
Error DSNU1221I - How do I use &variables (or equivalent) with a PATH expression in a TEMPLATE control statement?
I'm trying to unload all tablespaces from a small DB2 dabatase to Unix using the following command:
TEMPLATE TMP1 PATH '/tmp/unload_data/&TS..&PA..csv' FILEDATA TEXT RECFM VB LRECL 32756 PATHOPTS (OCREAT,OWRONLY),PATHMODE (SIRUSR) PATHDISP (KEEP,DELETE) LISTDEF LST1 INCLUDE TABLESPACE TESTDB01.TESTTS* UNLOAD LIST LST1 DELIMITED COLDEL X'3B' CHARDEL X'22' DECPT X'2E' ASCII UNLDDN(TMP1) SHRLEVEL REFERENCE
When I submit the JCL it finishes with the following error:
DSNU1221I 338 10:41:38.03 DSNUUNLD - UNLDDN IS NOT SPECIFIED OR SPECIFIED TEMPLATE TMP1 IS INVALID FOR MULTIPLE TABLESPACES
The same code works when I use a DSN with &variables instead of a PATH expression.
How do I use &variables (or equivalent) with a PATH expression in a TEMPLATE control statement?
Download a binary file from a z/OS UNIX System Services (USS) file system
I have a file in UTF-8 encoding with a certificate on z/OS in a UNIX file system and I would like to download it using a secure protocol.
I tried to download it using
scpbut the encoding has been automatically converted and the file was not readable.
How can I download the file as binary without changing the encoding?
How can I dump z/OS above-the-bar memory for debugging
I am writing a utility to exploit above-the-bar memory. I would like to dump the memory segments that I have allocated to help with debugging. SNAP and SNAPX refer to 24 and 31 bit addressing modes, but not 64. Forcing an abend using an ABEND macro or decimal zero divide provide very limited amounts of above the bar memory in the dump. Does anyone have experience dumping above-the-bar memory in 64 bit addressing mode? Do you have suggestions?