We just migrate from LUW to zos and we're finding that in z/os the accesspath explains show new node named WFSCAN (Workfile scan) and this rises when there is a join, group by or any kind of sort. The question is, Is any performance issue with WFSCAN. Should I be worry about the size of the pool it uses or any other costs in production environment? What should I do for better performance of Workfile Scans?

I need to check the size of some tables to get an indicative idea of how much space (in MB) they will occupy when I ingest them into another database. I have written this query (*) to obtain the aproximate size and the physical size informations. 1. Do you think this query is sufficient for my goal? 2. In the query, I noticed that I have a duplicate record because in the SYSTABLESPACESTATS table I have two different partitions. How should I consider them? Should I sum the two values to get the total size? Thank you! SELECT A.DBNAME, A.CREATOR, A.OWNER, A.NAME, A.TSNAME, A.TYPE, A.CARDF, A.AVGROWLEN, CASE WHEN A.CARDF -1.0 AND A.AVGROWLEN -1 THEN (A. CARDF * A.AVGROWLEN) / 1024 / 1024 ELSE CAST (NULL AS NUMERIC) END AS APPROX MB, B.UPDATESTATSTIME, B.STATSLASTTIME, B.PARTITION, B.NPAGES, C.PGSIZE, (B.NPAGES * C.PGSIZE) / 1024 AS PHYSICAL MB FROM SYSIBM.SYSTABLES A LEFT JOIN SYSIBM.SYSTABLESPACESTATS B ON B.DBNAME = A.DBNAME AND B.NAME = A.TSNAME LEFT JOIN SYSIBM.SYSTABLESPACE C ON C.DBNAME = A.DBNAME AND C.NAME = A.TSNAME WHERE A.NAME LIKE '%PIPPO%' AND A.TYPE= 'T' ORDER BY A.OWNER, A.NAME;

I have a new DB2 V12 database on z/OS 2.4 z13, with hundreds of programs to rebind, such as: sysibm.syspackage, sys.columns, sys.tables, etc. These will grow, hence the need to reorganize the tablespace for performance. Maintenance window is one hour, so just one tables space is being done. 1. Is reorg run on system tables the same as for user tables? 2. Is there special IBEGENR (sp?) VSAM redefine needed for system tables. Sample JCL:

//REORGS1 EXEC PGM=DSNUTILB,PARM='MT03,REORG1DB' 
   //STEPLIB  DD  DSN=DSNC10.SDSNLOAD,DISP=SHR 
   //SYSREC   DD  DSN=&&SYSREC,DISP=(NEW,DELETE,DELETE),       
   //             SPACE=(TRK,(9000,5000),,,ROUND),UNIT=SYSDA 
 LISTDEF TBLSLIST                                     
     INCLUDE TABLESPACES DATABASE TR032             
 REORG                                                
    TABLESPACE LIST TBLSLIST   COPYDDN(CDD1) LOG NO  
    SHRLEVEL REFERENCE                               
    SORTKEYS SORTDATA SORTDEVT SYSDA                 
    STATISTICS TABLE(ALL) INDEX(ALL)

`

I'm trying to load data into a DB2 table. The tablespace has 3 partitions with about 60M,60M,70M rows. The 3rd partition is failing to load. It gives me a warning that loading this partition's dataset will exceed the maximum size of # pages. The DSSIZE is currently set to 4G for this table space. To increase to 8G, I would need to change the storage groups to SMS. The data is currently partitioned by a numeric range. - P1 00-33 - P2 33-66 - P3 66-100 Would adding another partition and reorginize the data be a better solution? - P1 00-25 - P2 25-50 - P3 50-75 - P4 75-100 Are there any other suggestions that would help load this data?

If there are 3 tables that have foreign key relationship and I am giving row level permission on 1 table does that permission also apply to the other 2 tables automatically? Or do I need to give row level permission on all the 3 tables separately . for eg Table 1 Asia India Newyork US Table 2 nagpur india orange NJ US liberty table3 xxxx nagpur yyyy zzzz NJ ffff now if I give row level permission asia user, does it implies on table 2,3

I have scenarios where Table A and Table B can be updated as part of a single transaction or Table B can be updated on its own. I have a trigger on Table A for when there is an update on Table A and Table B as part of a single transaction. I have a trigger on Table B for when only Table B is updated. However, if Table A and Table B are updated and the Table A trigger fires the Table B trigger will also fire given Table B was updated. I'd like to avoid the double trigger fire. Is there a way I can check within the Table B trigger to check if it was fired as a result of the update NOT being part of a transaction?

I need to copy some Db2 tables programmatically (java 11 using the standard Db2 jdbc driver). The tables in question lie on a Db2 for z/OS database (v12) and on a Db2 LUW database (v11.1). The target tables exist and have all the required columns. Some tables contain rows defined like this: TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP In order to do the copy, I wish to use the Db2 load utility and row change timestamps need to be preserved. The syntax for this is identical for both Db2 types. When the table contains a row change timestamp, then the LOAD statement requires a ROWCHANGETIMESTAMPOVERRIDE modifier. All this has been gleaned from the IBM documentation. The difficulty is that I'm unsure how to tell whether the table in question has a row change timestamp. For Db2 LUW it is possible to query SYSCAT.COLUMNS, since this has a a field called ROWCHANGETIMESTAMP which would be either 'Y' or 'N'. That would mean checking each column in each table, but it's doable. However, it would be nice if there was an easier way to determine whether the table has a row change timestamp. For z/OS it isn't clear what should be checked. Is there a catalog query that would help? I can provide more details, if necessary.

We have a scenario where we would like to see if we could get hold of a list of tables affected by a given transaction. We use attunity to listen on the transaction log, but what I get from attunity is the transaction ID associated to a given event (a row change for a single table), not how many other tables that transaction has affected. Consider the below update events -

UPDATE DEPARTMENTS SET (DEPT_NAME) = ('ChangedName3') WHERE DEPT_NO = 'd010';

UPDATE EMPLOYEES SET (FIRST_NME) = ('Georgi2') WHERE EMP_NO = '10001';

UPDATE DEPT_EMP SET (FROM_DTE) = ('1986-06-26') WHERE EMP_NO = '10001' AND DEPT_NO = 'd005';

UPDATE SALARIES SET (SALARY) = ('60111') WHERE EMP_NO = '10001' AND FROM_DTE = '1986-06-26';

UPDATE TITLES SET (TITLE) = ('Sr. Engineer') WHERE EMP_NO = '10001' AND FROM_DTE = '1986-06-26';

COMMIT;

This generates 5 events each having the transaction id - 00000000C420BC4E86DD010000000100 (I used a sample from attunity output). Can I get something like - 00000000C420BC4E86DD010000000100, {EMPLOYEES, DEPARTMENTS, DEPT_EMP, SALARIES, TITLES} on each of the 5 events? Or query DB2 to find this information?

Is there a db2 function/procedure/variable that can be used to determine what platform a data source origins from? sysibm.sysdummy1 exists, so I assume that this is Db2, but some of the SQL does not look right according to LUW syntax. Is there an easy way to figure out via SQL what platform a data source origins from?

This is my first time posting in stackexchange, and I hope I did my due diligence for this question. I'm working on a problem that I just can't seem to wrap my head around. Consider this scenario of a chain of electronics stores, where day-to-day transactions are recorded in a database: Table Structure ---------------------------------------------- | daily_sales_records | ---------------------------------------------- | store_id | date |product_code| sales | | 1 | 2019-01-01 | 001 | 0.0 | | 1 | 2019-01-01 | 002 | 0.0 | | 1 | 2019-01-01 | 003 | 0.0 | | 1 | 2019-01-01 | 004 | 0.0 | | | | | | | 2 | 2019-01-01 | 001 | 0.0 | | 2 | 2019-01-01 | 002 | 25.5 | | 2 | 2019-01-01 | 003 | 12.0 | | 2 | 2019-01-01 | 004 | 0.0 | | | | | | | 3 | 2019-01-01 | 001 | 0.0 | | 3 | 2019-01-01 | 002 | 0.0 | | 3 | 2019-01-01 | 003 | 0.0 | | 3 | 2019-01-01 | 004 | 1.0 | | | | | | | 1 | 2019-01-02 | 001 | 0.0 | | 1 | 2019-01-02 | 002 | 0.0 | | 1 | 2019-01-02 | 003 | 1.0 | | 1 | 2019-01-02 | 004 | 3.0 | | ...... | | 1 | 2019-01-03 | 001 | 0.0 | | 1 | 2019-01-03 | 002 | 0.0 | | 1 | 2019-01-03 | 003 | 0.0 | | 1 | 2019-01-03 | 004 | 7.0 | ---------------------------------------------- (not shown: primary key, which would be the timestamp for each entry) Given this scenario, I am looking to retrieve all stores records with no sales of product codes 001, 002, 003 within that day, along with the dates when those occurred. Given the table above, the ideal output would be something like: ---------------------------------------------- | store_id | date |product_code| sales | | 1 | 2019-01-01 | 001 | 0.0 | | 1 | 2019-01-01 | 002 | 0.0 | | 1 | 2019-01-01 | 003 | 0.0 | | | | | | | 3 | 2019-01-01 | 001 | 0.0 | | 3 | 2019-01-01 | 002 | 0.0 | | 3 | 2019-01-01 | 003 | 0.0 | | | | | | | 1 | 2019-01-03 | 001 | 0.0 | | 1 | 2019-01-03 | 002 | 0.0 | | 1 | 2019-01-03 | 003 | 0.0 | ---------------------------------------------- (Store ID 2 is excluded on 2019-01-01 because there were sales for products 002 and 003, while store 1 was excluded on 2019-01-02 because there was a sale for product 003) Or - sorry, I am very rusty in DB2, but I do recall the possibility of generating a compact result table. If so, it would probably look like this: ------------------------- | no_sale_days | ------------------------- | store_id | date | | 1 | 2019-01-01 | | 1 | 2019-01-03 | | 3 | 2019-01-01 | ------------------------- The last experiment I tried worked for searching with only one store at a time(this is just off the top of my head, I don't have my notes with me at the moment, so pardon if there were any errors in the code): SELECT date, sum(sales) as salesum FROM DAILY_SALES_RECORDS WHERE STORE_ID = '1' AND PRODUCT_CODE IN ('001', '002', '003') AND SALES = 0 GROUP BY DATE HAVING SUM(SALES) = 0 Is it possible to expand this code to cover all store_ids in the daily_sales_records database? Or am I just thinking too hard on this one?

Is it possible to change SQL in a z/OS mainframe COBOL application so that it becomes eligible to be directed to the IBM System z Integrated Information Processor (zIIP)?

As a developer new to DB2, I was surprised when I discovered that I could not create my own tables in the development database environment. So, I posed a question to the db2 DBA. I don’t want to be too much of a nuisance by questioning policy and was hoping to get a better understanding of whether our policy of preventing developers from creating tables is justified for DB2. **Question:** I understand that you must follow policy, but do you know why our company might create a DB2-only policy to prevent DB2 developers from creating tables in their own schema while simultaneously allowing SQL Server and Oracle developers to do so? **Answer:** The mainframe is amore controlled environment than Oracle and SQL. All applications within our company share the DB2 subsystems. In Oracle or SQL, each application has its own instance and their testing would not impact another instance. -- DB2 mainframe does not create multiple tables in the same file like Oracle. So, on the mainframe if you were able to create tables in test, you could potentially use up all the dasd if you created a huge table and if you were to test against that huge table, it could blow out the subsystem sort space, edm pool,etc. that is sized for a test environment. --DB2 Mainframe applications share subsystems and all system resources. Each test db2 subsystem is sized according to the function of that DB2 subsystem… DB2T – testing, DB2F – System testing, DB2B – Production Support. They all have different sizing allowance. **Question to SO:** Re: “you could potentially use up all the dasd if you created a huge table” Doesn’t DB2 have the ability to limit space usage by schema to prevent a developer from using up all of the DASD (which I presume is Disk Access Storage device/aka disk space)? Looking up the acronym EDM and finding “Environmental Descriptor Manager” I presume that this is what contains all of the table definitions, like a Master database. Is this a valid concern that is specific to DB2? From the DBA’s answer, I generally understand that our architecture is such that each environment, eg, TEST, UAT, etc, does not run in a separate instance and one instance can affect the other, but it sound like if this is the case, then we already have this issue given that developers have the ability to insert millions of rows into tables that were created for them. Isn’t the real threat the size of the tables that I create and rather than the ability to create a table? I would think that a DB2 DBA should be able to limit developers to a safe level.

We have several application teams that are looking to do a re-write of large legacy applications. This is mostly COBOL going against DB2 for z/OS (v11). The COBOL code was generated using a tool, so we don't have any access to the source. Let's say the SQL cannot be changed for the sake of this question. They would like environments (dev/test/perf) where they can copy the existing data and test the re-written application (still using the same SQL) while still having a team supporting the legacy system through dev->test->perf->prod. Current SQL example: SELECT FOO FROM MYAPP.BAR; Because the table name was fully qualified with "MYAPP" instead of supplying it later, they cannot simply copy the code that they already have within the same DB2 and just change the schema name. They would like entirely new DB2 subsystems to avoid this issue. Is there any way to avoid this? Are there any bind parameters or other strategies I could employ to have duplicate schema names running on the same DB2 subsystem?

I would like to remove control characters (HEX \x00 until \x7F) from a DB2 data base field. For this I tried to apply the following translate-function: TRANSLATE(field, 'X', x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F') It does work for control chracters, but the function also translates question marks, and I don't understand why? Interestingly question marks are not replaced by "X" like the control characters but by a space \x20. Why does it behave like that? Original values: Text View: mhlkm Hex: 6D 68 6C 6B 6D 3F Text View in Hex Viewer: mhlkm? Translated values: Text View: mhlkm Hex: 6D 68 6C 6B 6D 20 Text View in Hex Viewer: mhlkm

How would I create the DDL for PostgreSQL for the following DB2 tables? It should be a one-to-one migration, so I would like to change only as little as possible at the schema (I have difficulties with the BLOB/CLOB datatypes and the default values). CREATE TABLE table1 ( field_1 DECIMAL(15) NOT NULL, field_2 ROWID NOT NULL WITH DEFAULT, field_3 DECIMAL(15) NOT NULL WITH DEFAULT, field_4 BLOB NOT NULL WITH DEFAULT, field_5 TIMESTAMP NOT NULL WITH DEFAULT, field_6 INTEGER NOT NULL WITH DEFAULT, field_7 VARCHAR(64) NOT NULL WITH DEFAULT, field_8 SMALLINT NOT NULL WITH DEFAULT, field_9 CHAR(60) NOT NULL WITH DEFAULT, field_10 DATE NOT NULL WITH DEFAULT, field_11 DECIMAL(12,3) NOT NULL WITH DEFAULT, field_12 TIME NOT NULL WITH DEFAULT, field_13 BIGINT NOT NULL WITH DEFAULT ); Second table: CREATE TABLE table2 ( field_1 DECIMAL(15) NOT NULL, field_2 ROWID NOT NULL, field_3 VARCHAR(2500) NOT NULL, field_4 CLOB );

Let's say I have a table with columns id, subid, state, value, where (id, subid) is a unique tuple, state may be 0 or some value 0, and value is just some number. I want to build a query that gives columns id, sum(value) over all entries with this id, and sum(value) over all such rows having state 0. Of course, I know how to build this using subselects to compute the sums. However, I wonder how to do this elegantly using some OLAP-specification. Since I am not yet used to OLAP-specifications, I am not very certain how to do this. Surely, something like select * from ( select id, state, sum(value) over (partition by decode(state, 0, 0, 1), sum(value) over () from table ) where state=0 would do the job. My questions: - Is there a better way to fetch the desired result? - Does this query perform better than the classical subselect-solution at all? (I am using DB2 z/OS. Unfortunately, I do not have permissions to use explain on this installation) Edit: Sample data and expected results: Consider the table id subid state value ------------------------------------- 1 1 0 1 1 1 0 2 1 2 1 4 2 1 0 8 The expected result would be a table which, for each ID, sums the values of all entries and the values of all entries where state=0. id sum w/ state=0 total sum ------------------------------------ 1 3 7 2 8 8

I have a query where I will be using the same value twice for one of the parameters. This query will be run under IBM Data Studio to see the results. Below is a sample query: SELECT * FROM SYSADM.STATISTICAL_TABLE S WHERE S.END_RECORD_TIMESTAMP BETWEEN CONCAT(?, '-12.00.00.000000') AND CONCAT(?, '-22.00.00.000000'); When run in Data Studio or any similar graphical query building environment I am prompted to enter values for both parameters, even though the values will always be the same for both. I believe it is possible to used named parameter markers in other RDBMSes to indicate that the value should only be given once. Example: SELECT * FROM SYSADM.STATISTICAL_TABLE S WHERE S.END_RECORD_TIMESTAMP BETWEEN CONCAT(?:queryDate, '-12.00.00.000000') AND CONCAT(?:queryDate, '-22.00.00.000000'); Is there a way to use named markers in DB2 for z/OS to indicate that the value will be the same so graphical tools only prompt for one input? On queries that have 20+ inputs, this can be time consuming. Is there a better way that I should be doing this?

All, I am relatively new to Red hat linux and I have SUDOer authority but unable to create DB2 database or other objects. How can I accomplish this task? Please give examples. Also, is there any known freeware or trial tool for converting mainframe databases and associated objects to DB2 LUW specifically on linux box? Thank you Thanks in advance for your response.