texts:
I am converting an SQL Server system to run on Oracle (11g). The system has all been set up, data loaded, etc. The Oracle system was loaded using all standard default values.

The amount of data is quite large (160m records in tables, etc.). The server is an HP DL380 with 1tb raid 1+0 and 96gb memory.

FYI SGA is currently running at 25gb and PGA 10gb with 40gb max allocated for the Oracle system.

The ONLY changes to the standard Oracle install were to increase REDO files from 50mb to 10gb and the TEMP file to 30gb to stop switching/checkpointing.

In testing, I find the inexplicable, in that an SQL insert statement using 3-4 tables takes over 90 minutes to run, whereas the same logic using cursors takes about 3 minutes!

This takes about 8 minutes for cursors and 2-3 minutes for SQL on SQL Server (which is what I expect).

In other words Oracle SQL is 30-50 times slower than a cursor. It gives me the impression that the Oracle system is very fast at OLTP (individual record select/inserts) but really slow at manipulating larger tables in SQL.

I am certain this is not the usual case, and so am asking, are there any Oracle 'parameters' that need to be adjusted?

Below is an example of the SQL used:

INSERT INTO TMP_TXN ......
SELECT .......
  FROM TXN (160m rows), CUSTOMERS (1m rows), PRODUCTS (1k rows)
 WHERE TXN.TXN_DATE = p_date
   AND TXN.TXN_AMOUNT > 0
   AND TXN.PRODUCT_ID = PRODUCTS.PRODUCT_ID
   AND PRODUCTS.PRODTYPE_ID IN ('1', '12', '13');
   AND TXN.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID
   AND CUSTOMERS.CUSTOMER_STATUS LIKE 'A%'

and

FOR item IN (SELECT .....
              FROM TXN (160m rows), CUSTOMERS (1m rows), PRODUCTS (1k rows)
              WHERE TXN.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID
                AND CUSTOMERS.CUSTOMER_STATUS LIKE 'A%'
                AND TXN.PRODUCT_ID = PRODUCTS.PRODUCT_ID
                AND PRODUCTS.PRODTYPE_ID IN ('1', '12', '13')
                AND TXN.TXN_DATE = p_date
                AND TXN.TXN_AMOUNT > 0
              ORDER BY CUSTOMER_ID);
  LOOP
     IF item.customer_id <> customer_id_old THEN
     etc.

Please don't get hung up about this example SQL. It is one of literally hundreds and EVERY SINGLE ONE runs slow. But when using cursors, the scripts run 10-50 times faster.

I checked the SQL and cursors side by side in sql developer. They are functionally identical and produce the same results.

When running the SQL, I look at the resource monitor and see that the Data tablespace is hardly touched (<1mb/sec) whereas the UNDO / REDO tablespaces are 3-50mb/sec. The REDO log files were resized to 10gb as they were previously switching about every 1-2 seconds!

I don't think the problem is the SQL. I think the problem is the Oracle SQL subsystem / the optimiser / memory managements, or something similar. Why? Well these are the common denominators for 100 pieces of code (not specific indexes, tables, etc.).

Something is wrong. Doing an internal Oracle function (backup/restore) has data transfers of 300-500mb/sec. Doing an SQL insert has data transfers of <1mb/sec. So my guess is that something on Oracle 11g is being throttled - and my thought was that there must be some configuration that is optimal for Oracle as a DWH with large data/table handling rather than for single insert OLTP.

As this is totally wrong, my guess is that there is some Oracle setup problem that I have missed. Any ideas for this incredibly slooooow performance from Oracle SQL?


texts: How do we get an hour, minute and second difference between two datetimes?

texts:
The table Task has columns as:
Task(TaskID, TaskName, CreatedDate)

And want to execute code block based on datetime difference as:

IF Task CreatedDate has passed 15 Minute THEN

Execute block #01

ELSE IF Task CreatedDate has not passed 15 Minute yet THEN

Execute block #02

I tested below query but it gives result as 61633440
SELECT DATEDIFF(MINUTE, CONVERT(VARCHAR(10), CreatedDate, 108), CURRENT_TIMESTAMP) AS MinuteDiff

How could we achieve above in sql?


texts: Sleeping sessions with an open transaction but empty input buffer

texts:
I have found out recently, that there are hanging several connections on our dev server (SQL Server 2016) with an open transaction. These transactions have been open for several days. I am using the query as follows:

SELECT session_id, se.database_id, status, open_transaction_count
FROM sys.dm_exec_sessions
WHERE open_transaction_count > 0
AND status = 'sleeping'
AND is_user_process = 1

It returns me several sessions with open_transaction_count=1. When I check the returned session_ids in the  sys.dm_tran_session_transactions and sys.dm_tran_active_transactions the existence of these transactions is proved in these views. All of the transactions have transaction_state = 2.

I would like to see the last SQL statement or batch within these sessions to identify what code in our application causes it and that is the issues. When I use:

DBCC INPUTBUFFER([mySPID])

I will receive a query for some of the session_ids, but no result for the others. The same result is when I try the query as follows:

SELECT se.session_id, se.database_id, se.status, se.open_transaction_count, qry.text
FROM sys.dm_exec_sessions AS se
LEFT JOIN sys.dm_exec_connections AS con ON con.session_id = se.session_id
OUTER APPLY sys.dm_exec_sql_text(con.most_recent_sql_handle) AS qry
WHERE open_transaction_count > 0
AND status = 'sleeping'
AND is_user_process = 1

The interesting is that when I switch to the „problematic databases“ (those with no query text returned) and execute DBCC OPENTRAN the result is „No active open transaction“. I assume that it could be a select statement within an explicit unclosed transaction and so such a transaction is not returned with DBCC OPENTRAN. However, I am not sure with this interpretation.

I would like to know, why I cannot get the last statement/batch for these sessions and whether there is another way (then above mentioned) of how I can get it?


texts: How to create a running balance column based on account number?

texts:
I have a stored procedure that returns account statement for one or multiple accounts
Here is the part I have issues with: returning a running balance in each record based on account number.
My query doesn't reset the balance for each account, how do I do this??

  SELECT
    acc_account_transactions.account_no,
      COALESCE(debit_balance) as debits
     , COALESCE(credit_balance) as  credits
     , (@running_balance := @running_balance + (acc_account_transactions.debit_balance - acc_account_transactions.credit_balance)) as balance
  FROM acc_account_transactions
  JOIN (SELECT @running_balance := 0) r
  ORDER BY account_no

When running this, I get something like this, but the balance doesn't reset on each account

account  debit           credit             balance
13  |2578.19        | 0.00      |  2578.19
13  |1500.00        | 0.00      |  4078.19
13  |1500000.00     | 0.00      |  1504078.19
13  |1500.00        | 0.00      |  1505578.19
14  |1500000.00     | 0.00      |  3005578.19  <--- reset before this
14  |0.00           | 13500.00  |  2992078.19
14  |0.00           | 13500.00  |  2978578.19
14  |33458.43       | 0.00      |  3012036.62
15  |0.00           | 100000.00 |  2912036.62  <--- reset before this
15  |18506.34       | 0.00      |  2930542.96
16  |946.20         | 0.00      |  2931489.16  <--- reset before this
17  |90364.77       | 0.00      |  3021853.93  <--- reset before this
21  |0.00           | 0.00      |  3021853.93  <--- reset before this
22  |0.00           | 0.00      |  3021853.93  <--- reset before this
23  |0.00           | 0.00      |  3021853.93  <--- reset before this
23  |1105500.00     | 0.00      |  4127353.93
24  |1327.00        | 0.00      |  4128680.93  <--- reset before this
25  |0.00           | 0.00      |  4128680.93  <--- reset before this

Thanks


texts:
The comment column of distributor.dbo.MSdistribution_history contains comments such as

<stats state="2" fetch="11554" wait="214007" cmds="17898546" callstogetreplcmds="106880">
    <sincelaststats elapsedtime="300" fetch="8" wait="291" cmds="1948689" cmdspersec="6495.000000" />
</stats>

The state value translates to

STATE   Indication
1       Normal Log
2       Reader Thread has to WAIT for Writer
3       Writer Thread has to WAIT for Reader

Is the comment telling me that the reader thread is waiting for the writer thread but has read 1948689 cmds since the last stats entry. Or is it telling me that the reader thread is waiting for the writer thread and the writer thread has written 1948689 cmds since the last stats entry?


texts: Improve Insert performance for 200 Million rows

texts:
How can the following insert statement be improved - which varies from 20mins on certain days to 70-140mins on most other days.

SELECT DISTINCT
    od.SourceID,
    od.OrderID,
    od.OrderLineNo,
    CASE WHEN mbew.vprsv = 'S' THEN
        CASE WHEN isNumeric(mbew.stprs) = '1' THEN convert(decimal (15, 2), mbew.stprs)/peinh ELSE NULL END
    WHEN mbew.vprsv = 'V' THEN
        CASE WHEN isNumeric(mbew.verpr) = '1' THEN convert(decimal (15, 2), mbew.verpr)/peinh ELSE NULL END
    ELSE NULL END as standardPrice,
    mbew.vprsv,
    mbew.stprs,
    mbew.verpr,
    mbew.peinh,
    od.currencyCode,
    CAST(exchangerate1 AS FLOAT) * (CAST(erf.toFactor AS FLOAT) / CAST(erf.fromFactor AS FLOAT)) as exchangerate1,
    t001.waers,
    'X3' as xfactory

into TempCostTable

FROM Order_Detail od (nolock)

INNER JOIN Order_Header oh  (nolock)
    ON od.SourceID = oh.SourceID
    AND od.OrderID = oh.OrderID

INNER JOIN eord   (nolock)
    ON eord.matnr = od.ProductID
    and eord.flifn = 'X'

INNER JOIN zsm_eqmupd_cntrl zsm2    (nolock)
    ON eord.lifnr = 'IVG'+ zsm2.werks
    AND zsm2.xfactory = 'X'

INNER JOIN mbew mbew   (nolock)
    ON mbew.matnr = eord.matnr
    AND eord.lifnr = 'IVG' +  mbew.bwkey
    AND mbew.lvorm != 'X'

INNER JOIN t001k   (nolock)
    ON mbew.bwkey = t001k.bwkey

INNER JOIN t001  (nolock)
    ON t001k.bukrs = t001.bukrs

LEFT JOIN ExchangeRate_Detail exchrt (nolock)
    ON exchrt.SourceID = '000001'
    AND t001.waers = exchrt.currencycode
    AND od.currencyCode = exchrt.ToCurrencyCode
    AND CASE WHEN isdate(oh.orderDate) = 0 THEN getdate() ELSE oh.orderDate END BETWEEN exchrt.StartDate AND exchrt.EndDate

LEFT JOIN ExchangeRate_Factor erf (nolock)
    ON erf.SourceID = '000001'
    AND t001.waers = erf.currencycode
    AND od.currencyCode = erf.ToCurrencyCode
    AND CASE WHEN isdate(oh.orderDate) IN ('0', '00000000') THEN getdate() ELSE oh.orderDate END BETWEEN erf.StartDate AND erf.EndDate

WHERE od.SourceID = '000001'


texts: Spread SQL Server data files across devices to improve performance?

texts: Would it improve performance of a database in SQL Server if I place the .mdf (rows data) and .ldf (log) files on separate sets of raided disks, as opposed to having both files on the same set of raided disks?


texts: What is the reason of Host 'host_name' is blocked in MySQL?

texts:
When I use incorrect password to connect to mysql server, and the test count is over the max_connect_errors, and I find the Aborted_connects is also arise as the count I test, but the host is still not blocked.

So does the Aborted_connects over the max_connect_errors then host_name is blocked?


texts: How to get the difference in days between 2 timestamps in PostgreSQL

texts:
I have a table named accounts. I am interested in retrieving those accounts whose status has not been updated since more than 10 days. I thought about this query and it seemed to work but I am not sure it's the best approach:

SELECT * from accounts
WHERE status = 'PENDING_PAYMENT'
AND (date_part('day', (now() - status_updated_at)) > 10);

One thing I don't like about this is the now() part. It would be ideal if now() was not calculated for each record and instead be a single timestamp value calculated at the beginning. Moreover, I would like this example to return 1:

Suppose that for one record the status_updated_at column has this value: 2015-10-05 23:00:00. And now() returns 2015-10-06 01:00:00. Despite being only some hours of difference I want the result to be 1.


texts:
Apologies for the long post but I've recently seen some strange behaviour with Service Broker when the routes it uses reference a DNS entry, and I am wondering if anyone has seen the same?

We have utilised Service Broker to send messages from multiple databases on Server A to a single database on Server B, which contain chosen data modifications.
Recently we wanted to upgrade the database on Server B from SQL Server 2008 R2 to SQL Server 2012.

The steps we took were:-

Setup log shipping for the database on Server B to a new database on a new server (Server C).
Disable the queues in Service Broker on Server B so that the messages stack in the sys.transmission_queue on Server A.
Take a tail log backup of the database on Server B (with norecovery).
Apply the last log backup to the database on Server C, then recover that database.
Update the DNS entry used by the routes in Service Broker to point to Server C.
Ping the DNS entry on Server A to ensure that it is resolving to the new server, Server C.
Enable the queues on Server C so that the messages can be passed from Server A.

The problem that we had was the messages continued to stack in the sys.transmission_queue.

We investigated the problem but could not find an answer. No errors in the transmission_status column in the sys.transmission_queue table and SSB Diagnose didn't highlight any issues.

It was only when I added a linked server (as, I admit, a complete stab in the dark) using the DNS entry (which successfully connected to Server C) that the messages started flowing.

All I can think of was that SQL Server had cached the old DNS entry and it wasn't until I created the linked server that the old value was over-written by the new one.

I should point out that this process worked when testing, the only difference was that the routes were using FQDNs instead of DNS entries.

Has anyone seen this behaviour before? If so, how did you resolve the issue?

Any help or advice would be most appreciated.

Andrew

EDIT - Adding Service Broker tag


texts: what are the SplitPage as Transaction Name?

texts:
when I run the function fn_dblog to get some info from the SQL Server transaction log - I am after what is deleting something sometimes only
- I have set up a server side trace for this now.

I thought I could see something related to my mysterious deleting from the transaction log, but they are backed up every 15 min...

anyway, while looking at the transaction log using the query below, I see this:

select * from fn_dblog (null, null)
where spid is not null
order by [begin time] desc

is it because the inserted rows did not fit in a page of the clustered index, and therefore had to be created somewhere else allocating a new page for them?

could this be an indication that the FILLFACTOR is too high?


texts: Why am I getting an error about truncation when running an SSIS job?

texts:
hoping someone can help me out. I have no idea what's going on with this job. It runs every week night and always comes back with the same errors.

There are about ten files that are written. We pull the data from our database then convert them to flat files and send it to a third party vendor. Our third party has reported back that all they can view on the error files are the headlines, but no info.

I ran the job manually in debug mode in ssms and there are absolutely no bugs in the package itself. the job succeeded just fine.

Some background - this job has been running with errors since july, which is around the time they set up a new school year as this job is for an educational setting.


texts: Need help importing SSIS packages in SSMS

texts:
I am in charge of creating a documentation for an ETL Process which involves a Remote SQL Server which is linked to a Postgre DB. In order to periodically update the data, Jobs have been setup using Server Agent. All this was done by someone who is no longer in contact. But now I want to go through/viewing what exactly is happening inside the SSIS packages.

This ETL Process is running on a remote SQL Server.

I have SQL Server 2014 DB Engine and SQL Server Mgmt Studio installed in my local machine and yes I did select the Integration Services feature while in the installation stage.

After some research I found that in order to view SSIS packages, I first needed to connect using Server Type as "Integration Services", but when I try do that I get an "Access is Denied" error message. In order to tackle this problem I tried to change Access Rights in the DCOM configurations but still the same result.

If I can import these packages to my local machine then I can use Visual Studio 13 to open and know what exactly each package is doing. (Correct me if I'm wrong)

Can anyone help me to import a package which is remotely stored in a server?

Please note that the Remote Server and my local computer do not have the same name and password.


texts: MongoDB problems recovering a member of the replica set

texts:
I have a sharded database with 2 replica sets (RS1 and RS2) each one of the RSs with 2 servers. I had a problem yesterday with one member of the RS2, the mongod instance crashed throwing an error. After that I tried to recover the member making it sync with the other member of the replica set (it took a long time to finish the sync) and then I'm getting the same error again:

Tue May  7 12:37:57.023 [rsSync]   Fatal Assertion 16233
0xdcf361 0xd8f0d3 0xc03b0f 0xc21811 0xc218ad 0xc21b7c 0xe17cb9 0x7f57205f2851 0x7f571f99811d
 /usr/bin/mongod(_ZN5mongo15printStackTraceERSo+0x21) [0xdcf361]
 /usr/bin/mongod(_ZN5mongo13fassertFailedEi+0xa3) [0xd8f0d3]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl17syncDoInitialSyncEv+0x6f) [0xc03b0f]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl11_syncThreadEv+0x71) [0xc21811]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl10syncThreadEv+0x2d) [0xc218ad]
 /usr/bin/mongod(_ZN5mongo15startSyncThreadEv+0x6c) [0xc21b7c]
 /usr/bin/mongod() [0xe17cb9]
 /lib64/libpthread.so.0(+0x7851) [0x7f57205f2851]
 /lib64/libc.so.6(clone+0x6d) [0x7f571f99811d]
Tue May  7 12:37:57.155 [rsSync]

***aborting after fassert() failure

Tue May  7 12:37:57.155 Got signal: 6 (Aborted).

Tue May  7 12:37:57.159 Backtrace:
0xdcf361 0x6cf729 0x7f571f8e2920 0x7f571f8e28a5 0x7f571f8e4085 0xd8f10e 0xc03b0f 0xc21811 0xc218ad 0xc21b7c 0xe17cb9 0x7f57205f2851 0x7f571f99811d
 /usr/bin/mongod(_ZN5mongo15printStackTraceERSo+0x21) [0xdcf361]
 /usr/bin/mongod(_ZN5mongo10abruptQuitEi+0x399) [0x6cf729]
 /lib64/libc.so.6(+0x32920) [0x7f571f8e2920]
 /lib64/libc.so.6(gsignal+0x35) [0x7f571f8e28a5]
 /lib64/libc.so.6(abort+0x175) [0x7f571f8e4085]
 /usr/bin/mongod(_ZN5mongo13fassertFailedEi+0xde) [0xd8f10e]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl17syncDoInitialSyncEv+0x6f) [0xc03b0f]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl11_syncThreadEv+0x71) [0xc21811]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl10syncThreadEv+0x2d) [0xc218ad]
 /usr/bin/mongod(_ZN5mongo15startSyncThreadEv+0x6c) [0xc21b7c]
 /usr/bin/mongod() [0xe17cb9]
 /lib64/libpthread.so.0(+0x7851) [0x7f57205f2851]
 /lib64/libc.so.6(clone+0x6d) [0x7f571f99811d]

Any idea of why this may be happening? How can I make this server sync and work? My last surviving server is now running as secondary, is there a way to make it primary for a while to get the data out of it?

Thanks in advance!


texts: Schema check or "DATE as separate table"

texts:
I'm dusting of my SQL, and would love to know if I've got my mental ER-diagram right.

http://sqlfiddle.com/#!2/c5c59

Basically, I've got 10-15 different datasets (actividades) from several locations (islas) over the span of a few months. My first instinct is to separate the many-to-many relationship between Islas and Actividades into an intermediary table, then reference(/index?) each dataset by location.

If this is the correct approach, I'm then wondering whether there is any benefit to have a separate date table (I guess this would be kind of like a calender). ie: the join between location, date and dataset would give one result, querying a date and location would give a list of all the activities that day and querying a single date would give all datasets from all locations.

Or should I just datestamp each datapoint (of each dataset) and deal with this in my queries.

I hope that was legible, thanks for your help!


texts: Should I duplicate the data or have linking table?

texts:
We have the following data:

Collections of items
Individual items

These will go into the following tables:

ItemCollectionTable (Id, Name, OtherFields (desc, date, etc..))
ItemTable (Id, OtherFields)

When an item is added the user will be giving it a collection straight away which is fine. We have a situation however, where users may want to take a whole collection and copy it into a new collection. To do this we've come up with these solutions:

Create a duplicate for every item in the Item table with the original scenario, only changing the PK and Scenario FK.
Problem obviously being duplicating data

OR

Use a linking table showing that an item is in two collections, only adding a new entry when data between the two differs.
The problem being the huge complexity of accessing the data but saves the duplicating issue above

Is there an obvious better solution or does the amount of data in the collection change the answer to go with? I'd be very grateful if there was another solution instead?


texts: Index Help - Plan has 2 index-scans, query is slow & high-wait

texts:
https://www.brentozar.com/pastetheplan/?id=rJpfNCsJl

The query above is identified as "expensive" and "slow" by monitoring tools, and I feel it could use some indexing help, but I'm not completely sure how.  sp_BlitzIndex doesn't give a lot of concrete recommendations for it, and of course Plan Explorer only identifies the problem just as I said it, which is those 2 nasty scans.

Does anybody have a recommendation on what index(es) to add that could help turn those scans into seeks, to squeeze some better efficiency out of this query?  Thanks!


texts: How to fetch the records which is belongs to only one group in postgresql

texts:
I want to fetch the student_id which is only belongs to one collage
expected op
6 and 7 stud_id's so how can i do this?

 _id | stud_id | collage_id | university_id  | active
-----+---------+------------+----------------+--------
   1 |       2 |          2 |              2 | t
   6 |       2 |          3 |              3 | t
   7 |       6 |          2 |              2 | t
   8 |       7 |          2 |              2 | t
   9 |       8 |          2 |              2 | t
  10 |       8 |          3 |              2 | t

and also if i need fetch the data which is belongs to perticular collage , i mean to use where clause ? thank you


texts: I have SQL file using PostgreSQL with size about 7GB. How can I open/execute the SQL file? I have researched and tried using Navicat Premium, Razor SQL, HeidiSQL, Workbench. In all I get out of memory. I know for sure it is a PostgreSQL database dump.


texts: Cassandra nodetool repair parallelism: "parallel" versus "dc_parallel"

texts:
If I run nodetool repair --full I see settings like this:

repairing keyspace some_keyspace with repair options
  (parallelism: parallel, primary range: false, incremental: false,
   job threads: 1, ColumnFamilies: [], dataCenters: [], hosts: [], # of ranges: 735)

But if I run nodetool repair --full --dc-parallel I see this:

repairing keyspace some_other_keyspace with repair options
  (parallelism: dc_parallel, primary range: false, incremental: false,
   job threads: 1, ColumnFamilies: [], dataCenters: [], hosts: [], # of ranges: 1992)

Note the parallelism setting. Since Cassandra 2.2, "parallel" has become the default. I was wondering what the difference is between "parallel" and "dc_parallel". The documentation implies that dc_parallel is faster because it operates in all DCs at once, but in my brief testing it seems to be the opposite; perhaps it is due to my replication settings – most keyspaces are set to use NetworkTopologyStrategy and place an equal number of replicas in each of my DCs – but I would like to be more sure than just a guess. "parallel" is not well documented.

For what it's worth, I am running Cassandra 3 (DSE 5.0.3.)


texts: update all rows after insertion in mysql

texts:
I am working with mysql and I have a table with the following structure (a summary):

CREATE TABLE `costs` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`utility` DECIMAL(9,2) UNSIGNED NOT NULL,
`tax` DECIMAL(9,2) UNSIGNED NOT NULL,
`active` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
`created_at` TIMESTAMP NULL DEFAULT NULL,
`updated_at` TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`id`),
)

where the active field defaults to 1 when inserting, then i would like when saving a new record all other rows update the active field as 0, so i try to create a trigger for this but i am getting a mysql error.

DELIMITER //
CREATE TRIGGER after_costs_insert AFTER INSERT ON costs FOR EACH ROW
BEGIN
  UPDATE costs SET active = 0 WHERE id <> NEW.id;
END;
//
DELIMITER ;

I think it is not possible to do this, so how can I update these rows?


texts: How to get SQL Profiler record the user which login to your web application but the SQL username and password is specify in the web.config file?

texts:
I have a web application that will retrieve data from a database. I have specify in the web application web.config file to have the username sa and password password.

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=sa;Password=password;

However, I notice that SQL Profiler will display the LoginName column as sa and not the actual user if a actual user log into the web application.

Example: Suppose a user WATSON\User1 come to the web application. The SQL Profiler will not record WATSON\User1 under the LoginName column. Then I will not know who have done something.

So, what should I do in order to have the SQL Profiler to record these user who login to the application from other domain?


texts: Display single record for attributes values joining 3 tables in SQL Server?

texts:
I have 3 tables

Table1 (a view) contains the record ID, Person's Name, Attribute ID and a new record for all attributes entered into the database pertaining to the person.

Table2 is a descriptive identifier name for the Attribute Type ID in Table1

Table3 (a view) contains the data entry timestamp for the record ID in Table1, used as selection criteria range

Table1 --SQL Server 2014 View--

ID [char(18)]
Name [nvarchar(50)]
AttributeTypeID [int]
AttributeValue [nvarchar(2000)]

Table2 --SQL Server 2014 Table--

AttrTypeID [int]
AttrName [nvarchar(50)]

Table3 --SQL Server 2014 View--

EntryID [char(18)]
EntryDateTime [datetime2(3)]

My current query brings them together (however I would like a different output):

SELECT DISTINCT
Table1.ID
Table1.Name
Table2.AttrTypeID
Table2.AttrName
Table1.AttributeValue

FROM
Table1 RIGHT JOIN Table2 ON Table1.AttributeTypeID = Table2.AttrTypeID

WHERE
Table1.ID IN (SELECT DISTINCT
             EntryID
             FROM Table3
             WHERE
             EntryDateTime BETWEEN '2018-05-01 00:00:00.000' AND '2018-05-07 00:00:00.000')

ORDER BY
Table1.ID ASC

Returns the following records:

ID     | Name  | AttrTypeID  | AttrName   | AttributeValue
123456 | Bob   | 201         | Hair Color | Blonde
123456 | Bob   | 202         | Eye Color  | Blue
123456 | Bob   | 203         | Height CM  | 180
123456 | Bob   | 204         | Weight KG  | 77.1
123456 | Bob   | 205         | Email      | bob@domain.com
123456 | Bob   | 206         | Profile    | Text...2000characters...
789000 | Sarah | 201         | Hair Color | Brown
789000 | Sarah | 202         | Eye Color  | Brown
789000 | Sarah | 203         | Height CM  | 155
789000 | Sarah | 204         | Weight KG  | 50.5
789000 | Sarah | 206         | Profile    | Text...2000characters...

Note Sarah does not have an entry for email (AttrTypeID = 205) in table 1

My desired output is create a query that returns:

ID     | Name  | Hair Color  | Eye Color  | Height CM  | Weight KG  | Email           | Profile
123456 | Bob   | Blonde      | Blue       | 180        | 77.1       | bob@domain.com  | Text...2000characters...
789000 | Sarah | Brown       | Brown      | 155        | 50.5       | NULL            | Text...2000characters...

Your assistance is very much appreciated!

PS I'm new here so if you need more info or require a different format for asking this question please let me know.


texts: Restore InnoDB tables / database using only data files

texts:
I have MySQL Server 5.1.51 installed on Windows Server 2003 as Operating System and using InnoDB as database engine.

I have run DROP DATABASE dbName statement by mistake. I have no backup of that database and not enabled binary logging on server. But I have used one data file for all database. I have access to that data file (ibdata1) and log files (ib_logfile0, ib_logfile0).

May I restore the database using data files and log files?

If yes, then How can I do that?
What are the steps that I have to follow to restore the database?


texts: When creating remote BLOB store is "RBSFilestreamFile" always the name of the file to be added to the FILEGROUP?

texts:
When creating a remote BLOB store in SQL Server (2008 R2) is "RBSFilestreamFile" always the name of the file when adding it to the FILEGROUP like in this query (this is the name I've seen used in every example I've found online, but I need to know for sure)?

ADD FILE (name = RBSFilestreamFile, filename = 'c:\Blobstore')
    TO FILEGROUP RBSFilestreamProvider

I'm asking because I'm working on an application for restoring SharePoint content databases and need to know if I can hardcode this string into the application.

I know you can create file groups and files with any name you want, but specifically for setting up RBS with SharePoint using SQL 2008's built in FILESTREAM provider are these names an expected convention? i.e. Will it work if I name my FILEGROUP and/or FILE something else?


texts: Restricting Database Access/Functions to User Groups?

texts:
We've been having issues with Databases being DROPed or RESTOREd WITH REPLACE with the incorrect backup etc...

I'm developing a plan right now on how to restrict access and I have some questions.

I'm familiar with SSMS but still am not super experienced with it, especially when it comes to "permissions".

We want everyone to be able to run querys/inserts/etc... on the databases and be able to take backups.

However, ideally we want them to have to submit a ticket to either restore a database or drop a Database completely. (And let me know if there's maybe something else I'm missing).

All the SQL Server instances are kept on a server. Lets call it TEST. On server TEST there are instances of SQL 2005/2008/2012/2008R2. Within server TEST there are separate Windows Server security groups; DEVS, QA, DBA, MANAGERS, etc.

Is there a way to restrict access to groups other than the DBA Group?  What would be the best way to do this?  We would want it restricted for the whole instance (Aka 2008/2012 etc..) instead of on a per database basis.


texts: Can a PRIMARY KEY constraint (with a NONCLUSTERED INDEX) check for only certain values?

texts:
Can I set a (logical) PRIMARY KEY constraint, served by a (physical) NONCLUSTERED INDEX, so that it only checks certain values of the constrained and indexed column or another column in the relevant table?

And, if so, can that column be established as a FOREIGN KEY constraint referenced from other tables?

I'm trying to basically see if I can remove certain rows from a parent table without removing those rows from a child table but still enforce the relationship for, e.g:

IDs > 1000, or
CreatedDateTime > RemovalDate.

For example, you can have that kind of constraint:

ALTER TABLE MyTable WITH NOCHECK ADD
    CONSTRAINT PK_MyTable CHECK (Id > 1000)

but how would you set this column as a PRIMARY KEY fixed with a NONCLUSTERED INDEX?

And can I do something like the following definition:

ALTER TABLE MyTable WITH NOCHECK ADD
    CONSTRAINT PK_MyTable CHECK (CreatedOn > '01/01/2010')

but have an INDEX for the column named Id?


texts: Rescue truncated data from incorrect column type

texts:
I have mistakenly inserted a variable 18-20 digit variable into an unsigned INT() column.

The data has been truncated to the max 32bit length so all my data is displayed as "4294967295"

Is there any way to retrieve the original data?


texts:
In a scenario where multiple short strings of text in table2 are to be associated with various rows in table1, is it better to use:

A through-table with 3 columns: id, table1_id, table2_id? This seems like the conventional approach, often preached by "avoid premature optimization" and "that's what RDBMs were built for" sayers who don't pay the AWS bill.
An array of integers corresponding to primary keys in table2? Seems like a naive solution that may be very optimized for sequential reads but a headache for any aggregating queries for daily reports etc.

Context:

A few weeks ago I was using the through-table. After some fiddling around in PgAdmin with explain-analyze, I decided to switch to using arrays of integers. The primary reasons were for simpler queries and sequential reads. But after reading this question, I'm now having second thoughts; is an array in Postgres effectively just an abstraction for a table within a row?

Usage:

These would be used for tags (like on StackExchange but less featured) and for address components (city names, street names, etc). 80% of transactions would be sequential reads (some will hit the cache), 10% reads to filter rows by any number of table2_id integers, and 10% writes (create/update/delete). Writes would happen in bulk for the most part. I think a through-table would make more sense if they were being added one-by-one at random intervals.


texts:
I am trying to configure the SQL Server Job Agent using the following scripts

USE [msdb]
GO
CREATE USER [mydbuser] FOR LOGIN [mydbuser]
GO
EXEC sp_addrolemember N'db_datareader', N'mydbuser'
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'mydbuser'
GO
EXEC sp_addrolemember N'SQLAgentReaderRole', N'mydbuser'
GO
EXEC sp_addrolemember N'SQLAgentUserRole', N'mydbuser'
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'xp_cmdshell',1
go
reconfigure with override
go
sp_configure 'show advanced options',0
go
reconfigure with override

use master
go
GRANT EXECUTE on xp_cmdshell to db_job
go

use master
go
EXEC sp_xp_cmdshell_proxy_account 'mydomain\db_job', 'password-1'
go

CREATE CREDENTIAL [Job3] WITH IDENTITY = N'mydomain\mydomainuser',
    SECRET = N'mydomain\mydomainuser'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'LW_JobRunner', @credential_name=N'Job',
    @enabled=1
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'LW_JobRunner', @subsystem_id=3
GO
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'LW_JobRunner',
    @login_name=N'mydbuser'
GO

/* Setting up job Category */

DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL',
    @name=N'MyJob'
go

USE MASTER
GO

EXEC master.dbo.xp_sqlagent_proxy_account N'SET',
    N'DEV',            -- agent_domain_name
    N'Administrator',  -- agent_username
    N'password'        -- agent_password

But the last step on executing the sp xp_sqlagent_proxy_account, says "Could not find the stored proccedure xp_sqlagent_proxy_account.

I'm sure I'm executing it on the master db with the sa credentials...

And because of this on adding a job I get an error like the

"Coud not find the server 'myIpAddress' in sys.servers. Verify that the correct server name was specified .

If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers."


texts: Creation of spatial index locks the table for hours

texts:
I used this code to create a spatial index in SQL Server 2008 R2:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE SPATIAL INDEX SPATIAL_COMPANIES_GEO ON dbo.ITEMS(GEO) USING GEOGRAPHY_GRID
 WITH(
          GRIDS  = ( LEVEL_1  = HIGH, LEVEL_2  = HIGH, LEVEL_3  = HIGH, LEVEL_4  = HIGH)
        , CELLS_PER_OBJECT  = 100
        , STATISTICS_NORECOMPUTE = OFF
        , ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON
     )
GO
ALTER TABLE dbo.COMPANIES SET (LOCK_ESCALATION = TABLE)
GO

This query executes in 5 seconds, but then the table ITEMS seems to be locked for hours. In Management Studio I cannot expand the Indexes folder for ITEMS and any query to that table times out. Furthermore, if I restart the  SQL Server service the spatial index I just created disappears.

I've tried this on my production database with 3.000.000 records and also on my test database with 300.000 records with the same result.

Is this normal? What I should do to create the index without locking the database?


texts:
I am required to retrieve the list of pno ( product_id ) for the products which were purchased by two customers. For this, I have been given two relations which are ( along with the attributes ) as follows.

odetails(ono,pno,qty)
orders(ono,cno,eno,received,shipped)

Here ono is the order_id, pno is the product_id, qty is the quantity of the product in stock, cno is the customer_id of the customer who is purchasing the product, eno is the employee_id who is selling the product, received is the date on which the customer received the product and shipped is the date when the product was shipped. ono is the primary key of orders and foreign key of odetails. ono, pno, qty form the primary key of odetails.

One query is:-

select distinct d.pno
from odetails d,orders o1,orders o1
where d.ono=o1.ono
  and d.ono=o2.ono
  and o1.cno<o2.cno;

Another query is:

select distinct d1.pno
from odetails d1,odetails d2,orders o1,orders o1
where d1.ono=d2.ono
  and d1.ono=o1.ono
  and d2.ono=o2.ono and o1.cno<o2.cno;

Could you please tell me what is the difference between these two queries? Both of them seem to work in the same way except the fact the second query seems to be doing extra and useless work of comparing two odetails when they are not even required to do so.


texts: Frequency of SQL DB Full and Differential backups

texts:
I want to know what would be the best frequency in taking SQL DB -  full and differential backups looking at below particular scenario.

I have seven databases and each needs to be backup.

Currently, I have setup a couple of maintenance plan in SQL as below.

Clean Full Backup every night at 1AM, older than 7 days
Full Backup every night at 2AM
Differential Backup every 1 hour between 5AM to 10PM
Clean Differential Backup Daily 10:30PM, older than 1 day

Considering the full backup size of each Database is 50GB. What would be the optimal way to take backups with 1TB of drive space.

Note that I have different drives for Full and Differential backups of size 1TB and 500GB respectively.

Any suggestions?


texts: How can I prevent mysqldump from prepending the database name in CREATE VIEW?

texts:
When I execute the following:

mysqldump -u root -n mydatabase > mydatabase.sql

The tables are all dumped without the database name, but the views oddly get the database name prepended:

/*!50001 DROP TABLE IF EXISTS `vfiscalperiodcombobox`*/;
/*!50001 DROP VIEW IF EXISTS `vfiscalperiodcombobox`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `mydatabase`.`vfiscalperiodcombobox` AS select `mydatabase`.`fiscalperiod`.`name` AS `text`,`mydatabase`.`fiscalperiod`.`id` AS `value` from `mydatabase`.`fiscalperiod` order by `mydatabase`.`fiscalperiod`.`start` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

Because of this I cannot import this file into another database (without manually removing the database names from the file).

This happens with the following MariaDB version on Windows:

mysql  Ver 15.1 Distrib 10.1.9-MariaDB, for Win32 (AMD64)

It does NOT happen with the following version on Debian:

mysql  Ver 15.1 Distrib 10.0.28-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Is this simply a bug? Or am I doing something wrong? Or is there a flag with which I can force mysqldump to never prepend the database name?


texts: How can i know the relationship in the database in sql server 2008?

texts:
I want to know how can I determine relationships between data which is not determined by the primary key in the design.

For example, I have three tables in the database like this :-

table 1: fields
table 2: area
table 3: location

All of the tables have data but the person who created the database did not explain the primary keys and the foreign keys in it.  How can I know the relationship between these tables?


texts: In what situation can snapshot restore be blocked after SET SINGLE_USER?

texts:
In my tests, I have the following script:
USE master;
ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [TestDB] FROM DATABASE_SNAPSHOT = 'TestDBSnapshot'

In some case that I am not able to understand, I get the following error:
Database state cannot be changed while other users are using the database 'TestDB'
RESTORE DATABASE is terminating abnormally.
Changed database context to 'master'.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

It seems the messages are not in the correct order (.NET issue?), but aside from that, what is the situation when I can get RESTORE blocked after SET SINGLE_USER? I would expect SINGLE_USER to give me exclusive access (and it works most of the time).
After this error, the DB is indeed in SINGLE_USER state (but not restored).


texts: How to prevent "ibdata files do not match the log sequence number"?

texts:
I am dealing with a very large set of databases that are all innodb.

I've enountered this on mysql restart too many times for my comfort:

ibdata files do not match the log sequence number

But I've clearly watched mysql shutdown properly just before the restart when that message happens.

Then it "repairs" right up to the original sequence number with nothing lost.

What is the best approach to deal with and fix this permanently?

Using Percona with innodb_file_per_table=1

Example log:

InnoDB: Initializing buffer pool, size = 80.0G
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 475575972691 and 475575972691 in ibdata files do not match the log sequence number 925369860131 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: Percona XtraDB started; log sequence number 925369860131

Note how the final log sequence number now matches what it thought was wrong in the first place, so there was 100% recovery?

So why is the log sequence not being properly written to ibd?

Is it possible shutdown is incomplete somehow?

Thank you for any advice.

ps. I always wonder if I should be asking this on serverfault or here? Is it okay I asked here?


texts: Reclaim disk space after drop table from database in mysql

texts:
I have this version of MySQL server:

mysql  Ver 8.0.15 for Linux on x86_64 (MySQL Community Server - GPL)

I use a some large tables. When I truncate database with truncate database <name>, I check the hard disk space and I've noticed my disk space is not reclaimed.

This is more information on my server:

SHOW VARIABLES LIKE 'innodb_file_per_table';
'innodb_file_per_table', 'ON'`

Any idea to resolve this ?


texts: hierarchical retreival of records in postgresql

texts:
I am using postgresql-9.2 . I have a table of products which stores the information about the products and its child table is content table , which contains the products linked to it.

PRODUCT MASTER TABLE

MASTER_ID                NAME

1                      PRODUCT1
2                      PRODUCT2
3                      PRODUCT3
4                      PRODUCT4
5                      PRODUCT5
6                      PRODUCT6
7                      PRODUCT7
8                      PRODUCT8
9                      PRODUCT9
10                     PRODUCT10

PRODUCT_CONTENT TABLE

PRODUCT_CONTENT_ID                     PRODUCT_MASTER_ID        CONTENT_ID
A1                                          1                       2
A2                                          1                       3
A3                                          1                       4
A4                                          2                       3
A5                                          2                       4
A6                                          2                       5
A7                                          3                       5
A8                                          3                       7
A9                                          3                       8
A10                                         5                       8
A11                                         7                       9
A12                                         8                       10

I am writing a query to retrieve all the products(ids associated with them) with their content products which should display something like this hierarchy

MAINPRODUCT                     SUBPRODUCT
    2                               3
    2                               4
    2                               5
    3                               5
    3                               7
    3                               8
    5                               8
    7                               9
    8                               10

I have written the following query

 SELECT PM1.PRODUCT_MASTER_ID AS MAIN,PC.CONTENT_ID AS CONTENT
    FROM PRODUCT_CONTENT PC
    LEFT JOIN PRODUCT_MASTER PM ON PM.PRODUCT_MASTER_ID = PC.CONTENT_ID
    LEFT JOIN PRODUCT_MASTER PM1 ON
    PM1.PRODUCT_MASTER_ID = PC.PRODUCT_MASTER_ID
    WHERE PC.PRODUCT_MASTER_ID IN
      (
       SELECT PC.CONTENT_ID FROM PRODUCT_CONTENT PC
       LEFT JOIN PRODUCT_MASTER PM ON
          PM.PRODUCT_MASTER_ID = PC.CONTENT_ID
       LEFT JOIN PRODUCT_MASTER PM1 ON
          PM1.PRODUCT_MASTER_ID = PC.PRODUCT_MASTER_ID
       WHERE PC.PRODUCT_MASTER_ID IN ('1')
      )

IT RETURNS SOMETHING LIKE THIS

MAIN            CONTENT

2                       3
2                       4
2                       5
3                       5
3                       7
3                       8


texts: Performance Schema results display intermittently

texts:
Running queries against the performance_schema MySql tables and I'm finding that query results display intermittently. The user I am logged in with has select permissions. Another developer, with a different user, with the same permissions, connected to the same database was able to query with results. We are only seeing results in the replicate database under their user account.
When we run SHOW VARIABLES LIKE 'performance_schema'; on the same database, on one account the value is OFF and the other the value is ON
We are using a AWS Aurora MySql database version 5.6.10


texts: How to schedule a weekly backup of a database only if it has been changed since last backup

texts:
I´m new to SQL Server administration.

I´d like to create automatically backup database every Sunday night, but only if there was any change in previous week (if not, backup is unnecessary).

Is there some way how to do that only with SQL Server, like some automatic procedure or something like that?


texts: Why is the output of this query being returned like this?

texts:
I have a T-SQL query :

select
    CAST(0 as float(53)),
    CONVERT(nvarchar(max),CAST(0 as float(53)),128)

the output of which is

0  ,  0.0E0

Can some one explain why it is not 0 , 0?  The database is SQL Server 2008.


texts:
I've created one database on my local system. consider database name is test. This database primary files are test.mdf, test_log.ldf. and contains ndf files too.

Then i've stopped sql server service. then copied these primary files to another server machine*(not ndf files).*

Now i've to create one database 'test1' on that server machine, and have to attach these test.mdf and test_log.ldf files.

create database test1 on primary ( file name='C:\test.mdf'), ( file name='C:\test_log.ldf') for attach go

this query executed in server machine. it says error as """ Msg 5120, Level 16, State 5, Line 1 Unable to open the physical file "D:\data\ExistTable\FG1_lt_4000.ndf". Operating system error 2: "2(The system cannot find the file specified.)".

Msg 5120, Level 16, State 5, Line 1 Unable to open the physical file "D:\data\ExistTable\FG2_4000_8000.ndf". Operating system error 2: "2(The system cannot find the file specified.)".

Msg 5120, Level 16, State 5, Line 1 Unable to open the physical file "D:\data\ExistTable\FG3_8000_inf.ndf". Operating system error 2: "2(The system cannot find the file specified.)".

Msg 1813, Level 16, State 2, Line 1 Could not open new database 'test'. CREATE DATABASE is aborted. """

now what to do???


texts:
I created a script that generates and outputs the required ALTER SYSTEM KILL SESSION commands that I need to execute.

main script: (without all the conditions, for better use here in SE)

SELECT 'ALTER SYSTEM KILL SESSION '||CHR(39)|| ao.SID ||',' || ao.SERIAL# ||CHR(39) || ' IMMEDIATE;'
FROM v$session ao where (length(trim(ao.username)) = 5
                and upper(substr(trim(ao.username),1,1)) = 'E'

output example:

ALTER SYSTEM KILL SESSION '418,30413' IMMEDIATE;
ALTER SYSTEM KILL SESSION '755,19371' IMMEDIATE;
ALTER SYSTEM KILL SESSION '809,6715' IMMEDIATE;

How can I execute the generated ALTERs ?

I'm using Oracle SQL Developer 4.0.0.13


texts:
I want to dump a database. and I use:

pg_dumpall

by using this code, data which has been stored on data base is scrolling in the terminal and it says:

PostgreSQL database dump complete

PostgreSQL database cluster dump complete

but I can not find any new .bak file any where. I also used:

pg_dumpall -f test

it goes to next line without showing any message.

finally, where is stored .bak file?!


texts: SQL Server 2008 R2 Transactional Replication "Cannot insert explicit value for identity column..."

texts:
Right now I'm having an "identity crisis" with transactional replication in SQL Server 2008 R2. The database is running in compatibility 90.  There's a table that has an identity column and won't replicate over. The error is "Cannot insert explicit value for identity column in table '' when IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544)".

The "not for replication" is set to true for that table. I can't find any settings for the articles to specify this as well.

Any ideas are appreciated.


texts: MDX Crossjoin filtering on different hierarchies from the same dimension is very slow

texts:
I have an MDX query that was written by an MDX novice that is excessively slow (the query, that is, not the MDX novice). And I am also an MDX novice. Here is the query:

SELECT
NON EMPTY
(
    [Measures].[Status]
)
ON COLUMNS,
NON EMPTY
(
    Filter
    (
        Crossjoin
        (
            {
                [Value1].[Value1].[Value1A],
                [Value1].[Value1].[Value1B]
            },
            [Value2].[Value2A].[Value2A],
            [Value3].[Value3].[Value3].ALLMEMBERS,
            [Value4].[Value4].[Value4].ALLMEMBERS,
            [Value5].[Value5].[Value5],
            [Value2].[Value2B].[Value2C],
            [Value6].[Value6].[Value6],
            [Value7].[Value7A].[Value7A]
        ),
        (
            [Measures].[Status]
        ) > 0
    )
)
ON ROWS
FROM [Cube]
WHERE
(
    [Value7].[Value7].[Value7A],
    [Value8].[Value8].[Value8A],
    [Value9].[Value9].[Value9A]
)
CELL PROPERTIES VALUE

I have very little knowledge of MDX, but through some trial-and-error, I have found that removing the two [Value1].[Value1] entries from the Crossjoin makes the query return quickly. Or, removing both the [Value2].[Value2A] and [Value5] entries also makes it return quickly. But, obviously, this is changing the query, so is not the solution, but perhaps it provides clues as to where I should look in terms of indexes or suchlike if there is anything like this in SQL Analysis Services (I'm more familiar with SQL Server databases)?

One thing I did try, is to put & before [Value1A] and [Value1B]. This caused the query to return very quickly with no results. Unfortunately, I don't know if this is correct because the query without this change takes too long to be able to see if there are any results. I don't know what difference & is supposed to make, but is this the obvious answer or does it change the query to be different? It is equally possible that the original writer of this query should have used & anyway, but never got to test the query with a real data set.

Any help would be greatly appreciated.


texts:
This works fine, except in the results, I don't want the rows where STILL_REQUIRED is 0.

I am using firebird.

Select ID, JobID, NAME, ORDERNUM, STARTAFTER, FINISHBEFORE,
       START_OFFSET_MINS,
       TOTAL_MINUTES_REQ -
             (SELECT coalesce(SUM(TABLE_PROCESSTIMES.END_TIME-TABLE_PROCESSTIMES.START_TIME) / 60, 0)
              FROM TABLE_PROCESSTIMES
              WHERE TABLE_PROCESSTIMES.PROCESSID = TABLE_PROCESSES.ID AND TABLE_PROCESSTIMES.START_DATE <= '24.04.2018'
             ) AS STILL_REQUIRED
FROM TABLE_PROCESSES
ORDER BY JOBID, ORDERNUM;


texts: When is it beneficial to move duplicate data to a reference table?

texts:
If there is column with values from some finite set S, for example S = { alive, dead, unknown } for a life_state column in a persons table, when is it beneficial to add a reference table?

Options:

Storing duplicated static data in a life_state column

simple strings: code on the application layer level would need to exist to get possible data values since SELECT DISTINCT could be missing some values.
ENUM: if you want to get all possible data values for life_state then you would need to run SHOW COLUMN.. from INFORMATION_SCHEMA to get all possible data values and parse that data on the application layer level or have duplicate code on the application layer level. If a new life_state value needed to be added we would need to ALTER the table and lock the whole table up in the process while it is being altered. No additional data could be stored for each life_state value should it be necessary.

Normalized Create a reference table life_states and a linking table back to persons table. The possible values could be derived from SELECT name from life_states; which would return 3 rows. Additional joins are now required to get the life_state for any persons instance. This is not only slower but requires longer queries/more code on the application layer level. Any additional values could easily be added if necessary, though it isn't anticipated. Furthermore, if some other data needed to be added to the life_state type it could be easily added.

Other considerations:

INT/ENUM indexing might be quicker/take up less space strings.

Related discussions:

Is it wasteful to create a new database table instead of using enum data type?


texts: RETURN value directly from INSERT with RETURNING clause

texts:
I have a function that ends with:

    INSERT INTO configuration_dates (
      cols...
    ) VALUES (
      values...
    ) RETURNING id INTO ret_id;
    RETURN ret_id;

And I would like to remove the INTO ret_id part, and instead do something like:

RETURN (INSERT INTO configuration_dates (
  weekly_date_configuration_id,
  "from",
  "to",
  price,
  activity_configuration_id
) VALUES (
  wdc_id,
  from_ts,
  from_ts + wdc.duration,
  wdc.price,
  wdc.activity_configuration_id
) RETURNING id);

But I haven't found how to achieve this.

EDIT: adding entire function

CREATE FUNCTION make_date_from_configuration(wdc_id UUID, from_date DATE)
  RETURNS INTEGER AS $$
DECLARE
  from_dow INT := EXTRACT(isodow FROM from_date); -- day of the week (1 - 7)
  wdc weekly_date_configurations;
  from_ts TIMESTAMP;
  ret_id INTEGER;
BEGIN
  SELECT * INTO wdc
  FROM weekly_date_configurations wdc
  WHERE id = wdc_id;

  IF FOUND
    AND wdc.valid_through @> from_date             -- starts in the date range
    AND get_bit(wdc.weekdays, from_dow - 1) = 1    -- valid day of the week
  THEN
    from_ts := from_date || ' ' || wdc.start_time;

    INSERT INTO configuration_dates (
      weekly_date_configuration_id,
      "from",
      "to",
      price,
      activity_configuration_id
    ) VALUES (
      wdc_id,
      from_ts,
      from_ts + wdc.duration,
      wdc.price,
      wdc.activity_configuration_id
    ) RETURNING id INTO ret_id;
    RETURN ret_id;
  ELSE
    RETURN NULL;
  END IF;
END;
$$ LANGUAGE plpgsql;


texts:
I am attempting to setup a PowerShell script within a SQL Agent job (SQL Server 2016). I'm using a third party module, DBATOOLS.psd1 which appears to be where I'm getting my syntax error. Currently my SQL Job has one line of code which is failing:

Import-Module e:\dbatools-master\dbatools-master\dbatools.psd1

Why is the job failing with the following error? This command works just fine when I run it through the PowerShell ISE.

Error Message: Unable to start execution of step 1 (reason: line(5): Syntax error).  The step failed.


texts: What Microsoft SQL Server 2008 R2 features do you need to install to get OLEDB Provider Linked Table Connectivity?

texts:
Which features do you need to install in your SQL Server 2008 R2 instance to get OLEDB Provider Linked Table Connectivity?

Specifically I'm trying to install the VFPOLEDB provider.  (And something tells me I also need a 32-bit instance).


texts: Create unique numeric token on PostgreSQL

texts:
I have a database where I want to generate unique tokens of six numeric characters. These tokens should be generated each time an invoice number is inserted in the invoice_no column. This would mean that every time I do an insert and the column invoice_no is not null, the token is generated.

I want the token to be to generate as many tokens as possible without a token being repeated.

create table ledgers( invoice_no varchar(10),
                      debit numeric,
                      credit numeric,
                      details varchar(20),
                      token char(6) );


texts: What's the values for the Freq_Interval column in MSDB.dbo.SysSchedules when Freq_Type is weekly and more than one day is selected in the schedule?

texts:
I'm reading up on the SysSchedules table in the Microsoft API, and am trying to incorporate using this table in a query.

In the documentation, the Freq_Interval column seems to be defined out for when a single day is selected in the schedule and the Freq_Type is weekly.
But what are the values for every other combination of days when more than one day is selected?

For example, I have a job which is scheduled to occur weekly on the days Monday, Tuesday, Wednesday, Thursday, and Friday.
I see in the MSDB.dbo.SysSchedules table, it has a Freq_Interval of 62 (which isn't listed in the documentation.)


texts: How do I display subtotals in cube browser in SSAS 2012 (Sql Server Data Tools)?

texts:
Apparently this feature was available in previous versions but has now been removed.

What are some alternatives?


texts:
Currently we have two SQL Server Standard Edition mirrored, one serving as 'hot-backup'. We are planning to migrate to a new cloud hosted solution, where we will use SQL Server Web Edition. So mirroring (or replication) is no longer an option.

We are looking into different types of poor man's replication, as we want to maintain the 'hot-backup' situation. We focus on replication through Transact SQL scripting and Transaction log distribution.

I'm a developer, not a database administrator and I'm struggling with figuring out the impact both methods will have on performance.

I hope anyone here can point me in the right direction.

Given that we have:

around 20 database
each around 1GB in size
the closer we can get to realtime replication, the better

What would be the pro's and con's of the two methods (mainly looking at performance)? Or is there another (better option) to achieve our hot backup?

I know "upgrade to SQL Server Standard or Enterprise" is the best option. I also know it's encouraged to ask questions in stead of starting a discussion in this community. Still I hope someone can help out!

Kind regards


texts: What a closure `F` of a family of functional dependencies is?

texts:
I have some issues to determine what a closure F of a family of functional dependencies is.

I know its definition is

  F⁺= {X → Y\F⊨X → Y}

One example

Let be the following relation

R(Student,Examination, Date) with the following set of functional dependencies:

F={D,St → Ex, Ex → D}

The relation is decomposed in R¹(St,Ex) R²(Ex,D)

Why is F¹= ∅  but F²={Ex → D}? I would have made: F²= ∅ from the definition of F⁺

I know from the community wiki that

  Functional dependencies requires that they must hold for every
  possible instance.

  From an instance, you cannot find the functional dependencies that hold in a certain relation schema

Therefore I don't know how to know what is the closure of a family of functional dependencies.

Another example would have been:

Second example

R(Course,Student,Birthday,Grade)

with the following set of functional dependencies:

F={C,St → G, St → B}

And the following relations:

R¹(C,St,G) and R²(St,B)

F¹{C,St  → G} and R²(St  → B) I would have done, from the definition:

F¹=F²=∅


texts: How to "UPDATE table SET col = (CASE WHEN ... THEN (SELECT col FROM table ORDER BY col DESC LIMIT 1) END)"?

texts:
How would I update a column using a CASE expression where the inner THEN clause needs to calculate a value based on that same column of other rows in the same table?

i.e. Here's a list table with six children ordered by position in ascending order.

CREATE TABLE list (
    id int unsigned NOT NULL AUTO_INCREMENT,
    parent_id int unsigned,
    position int unsigned,
    PRIMARY KEY(id)
);

-- parents
INSERT INTO list (position) VALUES (0),(1);
-- parent 1 children
INSERT INTO list (parent_id, position) VALUES
  (1,0), (1,1), (1,2), (1,3), (1,4), (1,5);
-- parent 2 children
INSERT INTO list (parent_id, position) VALUES
  (2,0), (2,1), (2,2), (2,3), (2,4), (2,5);

I'm trying to move one of the children to another parent. I need to adjust the old sibling positions to close the gap and set the position of the moving item to be last in the new parent's children (last being the one with the highest position).

For the reparenting operation, I have the below query. The subquery here seems to be ignored and the moving item's position isn't changed. I've tried other subqueries and functions like COUNT(position) and even CTEs. Most other options I've tried gave errors though.

-- move row 11 as a child of row 1, readjusting row 2's children
UPDATE list AS l1
  JOIN list AS l2
  ON l1.parent_id = l2.parent_id AND l1.position > l2.position AND l2.id = 11
  SET l1.position = (CASE
    -- reposition old siblings
    WHEN l1.parent_id = l2.parent_id AND l1.position > l2.position THEN l1.position - 1
    -- reposition self to position +1 more than row 1's highest child
    WHEN l1.id = 122 THEN (SELECT position FROM steps WHERE parent_id = 1 ORDER BY position DESC LIMIT 1)
    END),
  l2.parent_id = (CASE WHEN l2.id = 11 THEN 1 END);

I'm trying to do this all in one query (unless such a query would be terribly inefficient). The above query is the only way I've found to update both the position and parent_id for only the items that need those changes. The way it's set up, I think the l2.parent_id = part is being ignored by the sibling items, which is desired and seems like it would be efficient.


texts: "Disjoint constraint" interchangable with "covering constraint"?

texts:
I was wondering if there is any difference between disjoint constraints and covering constraints.

In this diagram, D in the triangle means 'disjoint'. Below it also states that 'Grad and Understad Covers Student'. Is it safe to assume that disjoint constraints and covering constraints mean the same thing?

Also, in case of overlapping constraint, is there an abbreviation like the disjoint constraint abbreviated as d in the triangle?


texts: Silent install of Oracle XE 11g fails with error -3

texts:
I'm trying to install silently Oracle 11g XE in the default location (C:\oraclexe).

PS D:\Profiles\xxx\Downloads\x.x\oraclexe\oraclexe-11.2.0> .\setup.exe /S /v/qn

However, the command generates a setup.log with an error -3 in it, without more information.

[ResponseResult]
ResultCode=-3
I've tried a lot of command alternatives, even by using f1 and f2 arguments, but it still does not work. No matter if it is on Powershell or the command line.

Looks like my issue sounds similar to this question, except the answer resolves nothing.

The normal GUI installation works well.

Do you know how I can resolve this thing ? What does the result code mean ?

Thanks for reading me.

Target software : https://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/xe-prior-releases-5172097.html


texts: Does ORDER BY COUNT(*) DESC suffer a penalty?

texts:
I understand that older versions of MySQL parse the following but has no effect:

INDEX (col DESC)

For the following example, does performance suffer and is there a way to overcome it?

  SELECT col, COUNT(*)
    FROM tbl
GROUP BY col
ORDER BY COUNT(*) DESC


texts:
So I asked this question:

Setting up Mongo with clustering

But I didn't know enough until I read the answers and did more research.  Is it better to use Map-reduce or sharding for fast-paced systems with ~ 100 queries all asked at the same time?  Sharding will displace my data across clusters and it appears it essentially does a map-reduce or sorts.  If I were to use map-reduce for such queries, should I do so with a cluster as well?  If so, how easy is it to start with one Mongo server, then later use a cluster and distribute the map-reduce tasks to the cluster?  Or am I confused in what I think I need to accomplish?


texts: How to pause the subscriber end of transactional replication

texts:
How do I go about pausing transactional replication on the subscriber end.

We have a publisher on Server A, a distributor on Server B and a subscriber on Server C. All are in the same network.

Some background, we have quite a busy BizTalk server where we replicate the data from the BAM parts to a separate database server which will be used for a customized web application for statistics, searching etc. Seeing as the tables in the BizTalk server are both quite large and missing indexes etc since they're basically just there to have data dumped into them we opted to have the second, replicated database in which we can apply various optimizations for reading the data.

One thing I've noticed though is that adding a basic, non-clustered, index takes forever and causes all kinds of locks (we're using SQL Server Standard so Online indexing isn't an option unfortunately) I was thinking that giving the database a bit of respite from all the write operations from the BizTalk data while creating the indexes would be a good idea (right or wrong?).

Question is, how do I pause the replication (without losing any data from the publisher) on the subscriber end while creating the indexes?


texts: Safely quoting type names to protect against SQL-injection

texts:
How would I quote the type name to protect against SQL Injection. For example, take this

SELECT FORMAT('SELECT CAST(%L AS %s);', '5.42', 'int');
                                                 ^ Quote this

That works fine, but if 'int' is instead ''int); DROP DATABASE foo;SELECT (' you'll have some problems,

SELECT FORMAT('SELECT CAST(%L AS %s);', '5.42', 'int); DROP DATABASE foo;SELECT (');
                          format
----------------------------------------------------------
 SELECT CAST('5.42' AS int); DROP DATABASE foo;SELECT ();
(1 row)

How would I go about casting to a type provided in run time safely with Dynamic SQL? If I use %I instead of %s I get an identifier (safe-quoting with ", but type-names are not identifiers and do not work with double-quotes).

Inspired by this answer to "PostgreSQL alternative to SQL Server’s try_cast function" provided by Jasen


texts: Go back in time from a PostgreSQL backup using xlog

texts:
I have a PostgreSQL backup and WAL archived before the db snapshot was taken.

Is it possible to restore the database using PITR to a time before the snapshot was taken?


texts:
I have an environment where I have to move data from one database to another once a day. I Try to solve this by calling 'dblink' from a triggered function.

The following statement I can execute from commandline:

SELECT fieldA, fieldB, fielbC
INTO temp_table
FROM dblink('dbname=dbname port=5432 host=a.b.c.d user=user password=pw',
            E'SELECT fieldA, fieldB, fieldC FROM data_table')
     AS temp_table(fieldA integer, fieldB integer, fieldC integer);

But when trying to embed this into a pgsql procedure it won't work.

I'm trying this by using the execute command:

CREATE OR REPLACE FUNCTION archive() RETURNS trigger AS $$
DECLARE
BEGIN

    execute
    '
      SELECT fieldA, fieldB, fielbC
      INTO temp_table
      FROM dblink(''dbname=dbname port=5432 host=a.b.c.d user=user password=pw'',
                  E''SELECT fieldA, fieldB, fieldC FROM data_table'')
          AS temp_table(fieldA integer, fieldB integer, fieldC integer);
    ';

    return new;
END;
$$ LANGUAGE plpgsql;

I get an error telling me that EXECUTE of SELECT ... INTO is not implemented and I could use EXECUTE ... INTO or EXECUTE CREATE TABLE ... AS

Does anybody has some experience with this? I'm struggling for hours now :-(


texts: Connect to a remote MySQL Server from a dynamic IP address

texts:
My ISP at home uses a dynamic IP address. I'm trying to access a MySQL database at work. How can I connect to it (using MySQL Workbench)?

I could grant all privileges to % so that any IP address will do, but I think this is NOT VERY SAFE. I don't know if granting privilege to my current IP Address will work.

If it does, it will still be tedious to grant privilege every time the IP changes.

Thanks in advance!


texts: Linked Server to SQL Server 2000 server from SQL Server 2012

texts:
I have a new SQL Server 2012 instance, and am migrating a database from SQL Server 2000. Some of the stored procedures in the DB I am moving reference another database back on that 2000 server. What is the best way to handle this?

Would a linked server help? I am having trouble creating a link for testing. Some people recommend using ODBC, others seem to use the SQL Server 10 client driver (SQLNCLI10). Using the SQL Server 10 client driver seems better to me, are there issues installing it on a SQL Server 2012 instance?


texts:
I have been looking for an answer about "Workload Groups" in SQL Server?

What exactly it is Workload Groups?

Could you explicit a real world situation when this term is used?

Thanks in advance your your help!


texts: What are all the catalogs that RDBMS have to implement to be ANSI compliant?

texts:
My goal is to be able to go into any environment, and immediately get a general idea of what is going on in a database server. I know most RDBMS implement INFORMATION_SCHEMA, which has a lot of useful information in it.
Is this the only vendor neutral catalog, or are there other helpful catalogs that return system metadata?


texts: Transactions in SQL Server Emergency Mode

texts:
I know that while the database is marked as suspected users cannot start new transactions, and the only access is via emergency mode which gives only read only access. I want to know how are  transactions handled in this case

firstly when a database is marked as suspected
secondly when we use emergency mode

Are they rolled back normally?

Thanks.


texts: if i'm change connection string to applicationintent:readonly then i setup read only routing list in my SQL server 2016 , does that mean my application will route all write request to primary and then read only request to secondary server which i setup as read intent only?


texts: Impact of changing the DB compatibility level for a Published replicated DB from 90 to 100

texts:
I have a SQL Server 2008 R2 server with a bunch of published databases that are currently operating under compatibility level 90 (2005).

The subscription databases are also SQL Server 2008 R2, however the destination databases are set to compatibility level 100 and replication is working fine.

If I change the compatibility level for the Published databases, will it affect replication in any way, or will it just be a case of reinitializing all the subscriptions and restarting replication?

I suspect that changing the published database compatibility level may change how the replication stored procedures function slightly, but I'm not 100% sure.

Is this the case?


texts: Does existing msrepl_tran_version column need to be removed when re-creating replication?

texts:
What is the process for removing a replication and then re-creating the replication. I know the process to set up replication. However, If i wanted to take a publisher database copy it and restore it and create another publication and replication how would I do that?

Would I need to remove the previous mesrepl_tran_version column from all the tables???

The type of replication that I will be re-creating is Transactional Replication with updateable subscribers.

Additionally, After I restore the database and create the publication do I need to let the snapshot complete and then back up that database and restore on subscribers and create the subscription

or

when I restore the database for the publisher can I also restore it on the subscriber?

Help will be greatly appreciated!


texts: How do I figure out what is consuming space in my Zenoss DB (mysql)

texts: I have been running Zenoss 4.2 and the filesystem has never exceeded 20gb. In the past 3 days it has shot up to 90gb and I am almost out of disk space. It appears the used space is all in /var/lib/mysql/ibdata1. First, how can I tell what is causing the system to grow so quickly, and how can I cleanup the space?


texts: Database Design: New Table versus New Columns

texts:
(This was suggested to be be repost here from StackOverflow)

Currently have a table .. and need to start adding new data columns to it.
Not every record (even going forward with new data after adding the new data columns) will have data.  So I am wondering if this is more suited for a new table since it is really an extension of some of the data rows and not applicable to every row.

In other words, since there will be a lot of unused columns for those new data elements, it seems like this would be more suited for a new table ?

The first table is a record of page views (currently 2million records)

- id
- IP address
- times viewed
- created_at timestamp
- date

for every IP address, a record is made per day - and consecutive pageviews are added to the times views per day

additional field(s) would be for point of origin tracking (ie google analytics source/medium/campaign)

Not every visit will have that info.
Im would assume about 10% of the rows will have the data (as it is usually only attributed on the first visit)

The main use for the data would be to attribute where people came from.  This may wind up being used more frequently (which then seems to lend itself to the single table)

Appreciate the feedback - can add more if needed


texts: SQL Server 2016 Multiple Availability Groups and instances

texts:
So we are getting ready to make a major architecture shift and I get to be on the hook for it, yay!

Currently we have 22 separate availability groups each with their own windows cluster across 44 servers.

We want to move to 6 servers to hold the 22 AGs, yes I know it doesn't divide evenly.

So for example a given server (VM) would be 4 SQL Server instances, each instance would be an availability group and have its own listener.

So something like:

NODE1: SQLINST1, SQLINST2, SQLINST3, SQLINST4
NODE2: SQLINST1, SQLINST2, SQLINST3, SQLINST4
AGs: SQLINST1, SQLINST2, SQLINST3, SQLINST4
Listeners: SQLINST1Lis, SQLINST2Lis, SQLINST3Lis, SQLINST4Lis

My questions are:

does each SQL Server instance also need to be its own windows cluster or could one cluster suffice for all the named instances?
Will this work at all?


texts: How to show the Availability group listener instead of the nodes?

texts:
I have just installed a sql server availability group, always on, with 2 servers,

SQLPROD1_TS and SQLPROD2_TS  as you can see on the picture below:

select distinct rcs.database_name,
ar.replica_server_name,
drs.synchronization_state_desc,
drs.synchronization_health_desc,
CASE rcs.is_failover_ready
WHEN 0 THEN 'Data Loss'
WHEN 1 THEN 'No Data Loss'
ELSE ''
END as FailoverReady

from sys.dm_hadr_database_replica_states drs

inner join sys.availability_replicas ar
       on drs.replica_id = ar.replica_id
      and drs.group_id = ar.group_id

inner join sys.dm_hadr_database_replica_cluster_states rcs
        on drs.replica_id = rcs.replica_id

order by replica_server_name

the listener for the availability group is calle SQLPROD_TS as you can see on the picture below

select * from
sys.availability_group_listener_ip_addresses

select * from
sys.availability_group_listeners

select * from
sys.dm_tcp_listener_states

all is good, however, when I ask people to connect to SQLPROD_TS when they browse the network they can only see the nodes of the cluster.

Is there a way I could make sqlProd_ts to show up on the picture above, instead of the nodes?


texts: How to enforce a constraint over multiple tables?

texts:
I have the following five tables:

PRODUCT
FEATURE
PROVIDER
PROVIDER_PRODUCT
PROVIDER_FEATURE

The logic is as follows:

A generic PRODUCT can have one or more FEATUREs. There are different PROVIDERs who are delivering the same product, but with different details, and the same FEATUREs, also with different details.

The problem is that with this ERD I can create a PRODUCT p1 with FEATURE f1 and a PRODUCT p2 with FEATURE f2. Then I create PROVIDER pv1. Now I can create a PROVIDER_PRODUCT pvp1 with PROVIDER pv1 and PRODUCT p1. But when I create a PROVIDER_FEATURE pvp1 I should only be allowed to add FEATURE f1, as this is the FEATURE that is linked to p1 via the PROVIDER_PRODUCT table. However I can also add a FEATURE f2.

How can I create a constraint that prevents the user from entering a PROVIDER_FEATURE that is part of a FEATURE that is part of a PRODUCT but that is not the PRODUCT on the PROVIDER_PRODUCT that is on that PROVIDER_FEATURE? Do I need to solve that in a stored procedure, or is there a more elegant way to enforce this?

+-------------------+       +--------------------+
|                   |       |                    |
| PRODUCT           +-----> | FEATURE            |
|                   |       |                    |
+---------+---------+       +----------+---------+
          |                            |
          |                            |
          v                            v

+-------------------+       +--------------------+
|                   |       |                    |
| PROVIDER_PRODUCT  +-----> | PROVIDER_FEATURE   |
|                   |       |                    |
+-------------------+       +--------------------+

          ^
          |
          |
+---------+---------+
|                   |
| PROVIDER          |
|                   |
+-------------------+


texts: DB2 for i equivalent of MSSQL's Show Query Execution Plan

texts:
I'm a DB2 newcomer and I'm hoping that DB2 for i has an equivalent to MS SQL's Show Query Execution path.
If so, what's it called, how do I access it, and what permissions do I need to use it?
Thank you!


texts: Restore database with .idb .frm and logfiles

texts:
I recently had an error with my SD card inside my RaspberryPi4. (I'm very inexperienced with databases as well.) So this means that I have to restore my database. What I did was copying all the files to the folder:

/var/lib/mysql

I used chroot to set the owner to mysql. However, now the serverice mysql does not start and gives an error on startup.

mysql.serviceJob for mariadb.service failed because the control process exited with error code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.

I checked journalctl -xe and it returned the following:

The job identifier is 799.
Jan 12 22:10:01 raspberrypi mysqld[1259]: 2020-01-12 22:10:01 0 [Note] /usr/sbin/mysqld (mysqld 10.3.17-MariaDB-0+deb10u1) starting as process
Jan 12 22:10:02 raspberrypi systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
-- Subject: Unit process exited
-- Defined-By: systemd
-- Support: https://www.debian.org/support
--
-- An ExecStart= process belonging to unit mariadb.service has exited.
--
-- The process' exit code is 'exited' and its exit status is 1.
Jan 12 22:10:02 raspberrypi systemd[1]: mariadb.service: Failed with result 'exit-code'.
-- Subject: Unit failed
-- Defined-By: systemd
-- Support: https://www.debian.org/support
-- The unit mariadb.service has entered the 'failed' state with result 'exit-code'.
Jan 12 22:10:02 raspberrypi systemd[1]: Failed to start MariaDB 10.3.17 database server.
-- Subject: A start job for unit mariadb.service has failed
-- Defined-By: systemd
-- Support: https://www.debian.org/support
--
-- A start job for unit mariadb.service has finished with a failure.
--
-- The job identifier is 799 and the job result is failed.

So currently I'm hesitating whether I should reinstall mysql, or that I need to do a couple of small things to get everything working.

The folder structure is as follows (mysql folder):

And one of the important DBs looks as follows (Freelobot):

Checking error.log yields

2020-01-13  1:12:38 0 [Note] InnoDB: Using Linux native AIO
2020-01-13  1:12:38 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-01-13  1:12:38 0 [Note] InnoDB: Uses event mutexes
2020-01-13  1:12:38 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-01-13  1:12:38 0 [Note] InnoDB: Number of pools: 1
2020-01-13  1:12:38 0 [Note] InnoDB: Using generic crc32 instructions
2020-01-13  1:12:38 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-01-13  1:12:38 0 [Note] InnoDB: Completed initialization of buffer pool
2020-01-13  1:12:38 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-01-13  1:12:38 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=36121288
2020-01-13  1:12:38 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-01-13  1:12:38 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-01-13  1:12:38 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-01-13  1:12:38 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-01-13  1:12:38 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-01-13  1:12:38 0 [Note] InnoDB: 10.3.17 started; log sequence number 36121297; transaction id 198032
2020-01-13  1:12:38 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2020-01-13  1:12:38 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-01-13  1:12:38 0 [Note] Recovering after a crash using tc.log
2020-01-13  1:12:38 0 [Note] Starting crash recovery...
2020-01-13  1:12:38 0 [Note] Crash recovery finished.
2020-01-13  1:12:38 0 [Note] InnoDB: Buffer pool(s) load completed at 200113  1:12:38
2020-01-13  1:12:38 0 [Note] Server socket created on IP: '127.0.0.1'.
2020-01-13  1:12:38 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist in engine


texts:
Wondering what database supports auto election of master from it's slaves or replicas?
I tried setting up Postgres and so far I learned that you have to manually configure which of the slaves or replica you may want to elect as master.
I would like to know if someone has gone into this kind of use case where you have a cluster of database master < slaves/replicas then once the master is down the slaves/replicas will auto elect for the next master. Then when the original master comes back up it will either be a slave or master.


texts:
I have a big table (which i created from 3 small tables) which looks like this

Acquisition | device | user_id | user_name
------------|--------|---------|----------
null        |   1    |    1    |   John
3           |   2    |    1    |   John
4           |   4    |    3    |   Mary
null        |   5    |    4    |   Snow

I want to know wich user have not made any  acquisition yet, for this example (4, Snow) would be the answer, as John has made the Acquisition 3.

I made this query

select user_id, user_name
from adquisicion_dispositivo
where acquisition is null and
user_id not in
(select user_id from adquisicion_dispositivo where
acquisition is not null);

But it takes too long (half hour and going ~1 million rows).
This table does not have any id's.

this is the schema of the table

  Columna      |          Tipo          | Modificadores
-------------------+------------------------+---------------
id_adquisicion    | integer                |
id_articulo       | integer                |
id_dispositivo    | integer                |
fecha_adquisicion | date                   |
id_usuario        | integer                |
version_android   | character varying(100) |
nombre_usuario    | character varying(100) |
fecha_nacimiento  | date                   |
sexo              | character varying(20)  |


texts:
I am unable to connect to the SQL Server instance. Logging into the box remotely I can see both CPU and MEMORY are maxed out. I have tried to restart the SQL Server service which has resulted it in "change pending" state which just seems to hang.
There are 2 SQL instances on this server, the other is operating fine. There are also Windows error logs for this instance which read

There was a memory allocation failure during connection establishment. Reduce nonessential memory load, or increase system memory. The connection has been closed.
SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.

Any ideas?


texts: Problem trying to force the use of a filtered index

texts:
I was trying to do some testing on filtered indexes yesterday and created a fairly simple situation:

CREATE TABLE IndexTest
(
    ID INT NOT NULL IDENTITY(1,1) CONSTRAINT pk_IndexTest PRIMARY KEY,
    Col1 CHAR(1)
)
GO

CREATE INDEX ix_IndexTest ON IndexTest(Col1)
WHERE Col1 IS NOT NULL
GO

INSERT INTO IndexTest VALUES ('A'),('B'),('C'),('D')
INSERT INTO IndexTest VALUES (''),(''),(''),('')
INSERT INTO IndexTest VALUES (NULL),(NULL),(NULL),(NULL)
GO

I then tried to run the following query using a hint to force it to use the filtered index.

SELECT *
FROM IndexTest WITH (INDEX(ix_IndexTest))
GO

But I keep getting the error:

  Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

It works if I remove the filter but not with it. I even tried changing the filter in various ways such as WHERE Col1 = '' etc. Any time I have a filter I get that error. Can anyone tell me why? Am I doing something wrong?

I tried this on a SQL Server 2012 and 2014 instance and created a sqlfiddle of it here:

http://www.sqlfiddle.com/#!6/4a850/1

Edit: Not sure that it matters but specifically I was trying to see if I could see what rows were actually contained by the index. The idea came from seeing someone use this technique to recover data (using an unfiltered index) from a corrupted table.


texts:
I have successfully setup a scale out deployment of 2 2016 Enterprise Edition SSRS servers connecting to a single shared database. So far, I have not setup a URL for the servers (they are each using their own local server name). Before I do that I'd like to better understand how this works.  There will not be a huge load on the server but it will be busier at certain times of the day. I am doing the scale out more for high availability than for any other reason.

Do I use the same URL for both the Server1 and Server2 Web Services and Web Portals
If I do use the same URLs and they are not load balanced, which server will the traffic be routed to or will it be random?
Could I use 2 different URLs and using one for Subscriptions and the other for ad-hoc report runs then switch the DNS pointers if there is a problem with one server.

This Microsoft Article describes it in general but doesn't answer these questions specifically.


texts: Where to start learning to become a successful Oracle DBA

texts:
I am IT Graduate with good knowledge on SQL and C. I have done most of the SQL programming on Microsoft SQL Server in my academics. But i want to become Oracle DBA. Could you please suggest me a structural way to to start learning.  And also please suggest me what background work is needed before digging into it.
Thanks


texts: What does a NULL last_executed column mean in the Oracle data dictionary table sql_plan_baselines?

texts:
Using Oracle DBMS 19c on Windows server.  The static data dictionary view DBA_SQL_PLAN_BASELINES has a lot of rows (over 160k) and I'm trying to figure out why. The vast majority have a LAST_EXECUTED value of NULL, and this number is growing over time.  The Fine Manual says for this column

Timestamp when the plan baseline was last executed

Note: For performance reasons, this column is not updated immediately after each execution of the plan baseline. Therefore, the plan baseline may have been executed more recently than the value of this column indicates.

Does anyone know how to interpret a NULL?  Was the plan generated but never used?  Is this at least partly some sort of upgrade left-overs (this instance has been upgraded through export/import through multiple DBMS versions)?  Any other clues?
thanks!
Martin


texts: what is a safe way to stop the logreader?

texts:
Applying cumulative updates and service packs on sql server 2016 -

The server is involved in transactional replication, being a publisher,

The server is also a virtual machine.

I need to stop the logreader application in order to carry on with the updates.

what could be a safe way to get this done?


texts: Same statement yields NULL when in a stored function, and non-NULL valid result otherwise

texts:
I have the following stored function for calculating ELO ranking:

CREATE FUNCTION spodziewanyWynik(mój INT, przeciwnika INT)
RETURNS DOUBLE DETERMINISTIC
BEGIN
  DECLARE wynik DOUBLE;
  SET wynik =
    1.0E0 / (1.0E0 + POWER(1.0E1, przeciwnika - mój + 0.0E0) / 4.0E2);
  RETURN wynik;
END//

CREATE FUNCTION nowyRanking(mój INT, przeciwnika INT, wynik DOUBLE)
RETURNS INT DETERMINISTIC
BEGIN
  DECLARE wynik INT;
  SET wynik =
    mój + ROUND(3.0E1 * (wynik - spodziewanyWynik(mój, przeciwnika)));
  RETURN wynik;
END//

Now I want to test it. I run:

SELECT nowyRanking(1500, 1500, 0.0E0);

And I get a NULL, for reasons unknown to me.

And now the fun part. I run:

SELECT 1500 + ROUND(3.0E1 * (0.0E0 - spodziewanyWynik(1500, 1500)));

And I get: 1470. Whew, that’s quite an improvement from a NULL, isn’t it?

Why on Earth does this happen? How should I fix my stored function?

Thanks in advance.


texts: Temprary tables on Mariadb with no activity on the server

texts:
I have a nagios check for mariadb 10.4 server running on Debian9, WARNING - 59.92% of 670 tables were created on disk | pct_tmp_table_on_disk=59.92%;50;75 pct_tmp_table_on_disk_now=77.46%. However, there's little to no activity on the server, slow query log doesn't registered anything.
In my.cnf I have tmp_table_size          = 32M and max_heap_table_size     = 32M.
The tmpdir is /tmp and i see nothing there as well.
I am not sure if the check is wrong or is there anything that i'm missing...Any suggestion would be appreciated


texts: Choosing MyISAM over InnoDB for these project requirements; and long term options

texts:
Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific.

My project's aim is to let users search a (huge) database of variety of products.

Each product is present under a category.
Each product will have 10 to 100 'specs' or 'features' by which the users will search.

The most common usecase is:

User clicks on a category; then clicks various sub-categories if required.
User starts off with 1 or 2 criteria and searches for products.
User then keeps adding more criteria to the search to narrow down on the product.

I have three main tables 'products', 'features_enum' and 'features'. It is very important to let the data-entry users, create new 'features' on the fly, for the products - hence I am using EAV (anti)pattern.

Here are the structures of the tables:

'products'
    ID(PK), TITLE, CATEGORY
    (Indexed by CATEGORY)
'features_enum'
    ID(PK), TITLE
'features'
    P_ID, F_ID, VAL
    (Indexed by P_ID and then F_ID)

A sample format of my main search query:

SELECT
  p.ID,
  p.TITLE PROD_TITLE,
  fe.TITLE FEATURE_TITLE,
  f.VAL
FROM
  products p, features f, features_enum fe
WHERE
  p.CATEGORY = 57 AND
  p.ID = f.P_ID AND
  f.F_ID = fe.ID AND
  (
    (f.F_ID  = 1 AND f.VAL = 'Val1') AND
    (f.F_ID  = 2 AND f.VAL = 'Val2') AND
    ...
    (f.F_ID  = N AND f.VAL = 'ValN') AND
  )

My Experimentation So Far:

Due to my limited knowledge and experience in DBs, I hit a wall with theoretical planning. So, I generated a large set of test data to simply see what will work. All three tables had 500,000 test rows. Here are the avg. run times of the main search query:

InnoDB without indexing: 90s.
InnoDB with indexing: 15s. 0.3s after buffer pool size increase
MyISAM without indexing: 9s.
MyISAM with indexing: 0.7s.
MyISAM with indexing + FIXED row type: 0.16s.

Test machine - Pentium 4 1.9GHz, 1.5GB RAM, IDE HDD, Win7.

I have basically not done anything to optimize other than indexing. So there might be a ton of things that I missed, that could have made InnoDB run faster. InnoDB buffer pool size was set to 16M (!!); I incremented it to 128M. Now, InnoDB is really fast. So one big reason for me leaning toward MyISAM is now gone. Maybe there is more that I can do.

Some points and long term usage estimates about the project:

20 new products added daily, at roughly 20 x 100'specs' = 2000 record writes per day.
1,000,000 page visits, and in worst case - same amount of search query runs per day.
Total record count for the tables is expected to reach 5,000,000 each.
Writes will be made by a semi-controlled group of people, where as read is public.
There are no complex 'transaction' type writes. The most complex write I can think of right now is - [one product row + 100 feature rows max] - at one shot
Require only a couple of constraints, but if necessitated by choice of MyISAM, I can enforce them at the application level itself.
DB access from other parts of the app - user registration, authentication etc.. will be few and far in between, I don't think they will have much effect.

Given all of that, I am biased towards MyISAM. But I need input from people already experienced in MySQL.

Questions:

If the InnoDB run times are wrong/surprising, what have I missed in
testing? Increasing buffer pool size dramatically increased performance. See above.
If not, considering all of the above, is MyISAM really good
choice in the long run?
If MyISAM too turns out to be a bad choice
later, how easily can I restructure the database? What options do I
have?

On a side note:

If choosing EAV was bad, what other architecture can I use for this project?


texts: MySQL - Backup new and modified entries only

texts: I am developing a very huge system in Java using Hibernate and MySQL. Now, I want to automate the daily, weekly, and/or monthly replication (backing up) of the data that are stored from the database using the system. I am very aware that this could be done easily using the mysqldump command, which could be easily incorporated in Java. But since the data that are handled and stored are very enormous, it came to my mind lately the possibility of backing up only those records that are newly added or modified from the last accomplished dump. Now I'm soliciting for your ideas or procedures on how could this be done. Thanks.


texts:
is there a way to improve the performance of a recursive CTE like below, I am unsure if I can add indexes on joins when the joins are using ROW_NUMBER?

DECLARE @File_Name VARCHAR(8000),
        @Disk VARCHAR(5)
SET @File_Name = 'MARSQLUTILITY,AdventureWorksDW_Data'
SET @Disk = 'I:'
--Code to pull out deltas between collected IO stats.
;WITH IOPS   ([IO_STALL]
           ,[IO_STALL_READ_MS]
           ,[IO_STALL_WRITE_MS]
           ,[NUM_OF_READS]
           ,[NUM_OF_WRITES]
           ,[SIZE_ON_DISK_MB]
           ,[DBNAME]
           ,[NAME]
           ,[FILE_ID]
           ,[DB_FILE_TYPE]
           ,[DISK]
           ,[FILE_LOCATION]
           ,[TIMESTAMP]
           ,[ROW])
AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY FILE_LOCATION ORDER BY TIMESTAMP DESC) AS [ROW]
FROM dbo.DISKIOPS
)

--Need to divide by the number of operations in that timeframe to get average wait time per operation.
--SELECT MAX(([IO2].[IO_STALL] - [IO1].[IO_STALL]) / (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES))
SELECT [IO1].[TIMESTAMP],
       [IO1].[NAME],
      ([IO2].[IO_STALL] - [IO1].[IO_STALL]) / (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES) AS Avg_Stall_Per_Operation
FROM IOPS IO1 JOIN IOPS IO2 ON IO1.ROW = (IO2.ROW+1)
WHERE IO1.NAME = IO2.NAME
--Need to make sure not dividing by 0 when there has been no operations
AND (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES) > 0
AND IO1.Disk = @Disk