texts: Date getting truncated on PDI 7.1 for salesforce DB - Need workaround

texts:
We are working on transformation which inserts date into a salesforce DB and are seeing issues.
Below are the issues: A transformation working in PDI CE 6.1 do not work in CE 7.1 or CE 8.3.
Error :

2019/10/31 15:46:32 - Salesforce Insert.0 - :Errors were found on item 0 Error code is: INVALID_TYPE_ON_FIELD_IN_RECORD Error message: Job Start Time: value not of required type: 2019-10-31T15:46:29.211Z

On 6.1:
The script was inserting a string variable into date field on SF DB and was working fine.
For 7.1:
We changed string to date datatype using select values step and executed, the step now inserts data but we saw that the date is shown as one day prior and time as 8PM
For 8.3:
We changed the date generation step to a javascript step, then changed the data type to date. here the insert works ok. Now if we run the transformation in 7.1 it again truncates the time field.
This seems to be the defect - https://jira.pentaho.com/browse/PDI-16480
We would like to know If anyone have encountered this issues, or Is there any workaround for this issue in 7.1 ? i.e. can any Jar file for salesforce be copied from 6.1 or 8.3 version to 7.1 to make this work?


texts:
This is my query:

select d.department_id, d.department_name, sum(e.salary) AS total
from department d inner join employee e on d.department_id=e.department_id
group by d.department_id,d.department_name

I want the Grand total by sum of total column (the alias column). I get this by using this query:

select d.department_id, d.department_name, sum(e.salary) AS total
from department d inner join employee e on d.department_id=e.department_id
group by d.department_id,d.department_name WITH ROLLUP

But I want this by a another column, like using the sum function. Is it possible?


texts:
I am updating a table with 250 million rows in SQL Server, and it's been running for 4.5 days. I'd like to use the computer it's running on for other memory-intensive work, so I'm torn about whether to cancel the query (which might also take days, right?) or let it keep going. Does anyone see anything in the code below to make you think it's in a never ending loop or something? Will canceling it take so long it's not worth doing?

Here is the code:

update A.Large_Table
set [Large_Table].var1
= [Segment_Info].var1
from A.[Large_Table] left join [A].[Segment_Info]
on [Large_Table].id = [Segment_Info].id
where [Large_Table].var1 is null;

Var1 and id in both tables are integers. I have successfully done this query on a 100 million row table in the past, and it took maybe 2 hours, but in that case the join was performed on an 8-character string column (instead of the 'id' integer). I would have thought that switching to an integer would greatly improve performance and I could get the results for a larger table in comparable time.

Once I get past this run and either cancel or it finishes, I am aware of a few things to make this go better in the future (though it's not an operation I have to run often):

Use insert into or select into a new table rather than update the existing one
Move my log file to a different drive than the database (I'm still learning such basic things...)
Restart SQL Server so the temp file clears

Any other things to try? Any of those above not likely to actually give a boost?

Thanks for your help!


texts: Converting a date to a datetime in MySQL

texts:
I'm supposed to add time to a date field. We use MySQL with python3, alembic and sqlalchemy. Now I've written a conversion program. It is my first program of such a kind. I'm not certain how it will work, how I can also migrate the data to the new field or if that is a different scenario.

I'm not sure about ? downgrade , what should it do if it is downgrading? In the examples I've looked at examples where nearly all do an ADD TABLE and therefore at the downgrade those function just delete what they created. But what about an ALTER tatement, how can I roll it back in the downgrade function if downgrading (which probably not will happen).

Is there anything else that I should think about?

def upgrade(**args):
    db_update_context = mnet_get_required_argument("db_update_context", **args)

    if not mnet_column_exists(table_name, column_name, **args):
        sql = textwrap.dedent("""\
                ALTER TABLE {} MODIFY COLUMN `{}` datetime;
                  """.format(table_name, column_name))
        mnet_execute(db_update_context, sql)

        sql = textwrap.dedent("""\
                ALTER TABLE {} MODIFY COLUMN `{}` datetime;
                  """.format(table_name, column_name2))
        mnet_execute(db_update_context, sql)

def downgrade(**args): # TODO: What is supposed to happen here
    columns_to_drop = [column_name]
    for column_to_drop in columns_to_drop:
        medinet_drop_column(table_name, column_to_drop, **args)


texts: Issue with MySql upgrade from 5.1 to 5.6 for Jboss SOA - p v 4.3

texts:
We are using JBoss SOA P v- 4.3, we have recently upgraded Mysql version to 5.6 from 5.1 and are facing some issue with the jboss start up.

We are using MySQL instead of Hypersonic database for Jboss in-memory database which jboss used internally, MySQL is recommended for production use.

This Database is used by JBoss Enterprise SOA Platform to persist registry services and the message store.

Therein we have a table named “AuthToken” having a column “CREATED”, details below.

Column Name : CREATED
Type : timestamp
Default : CURRENT_TIMESTAMP

We are getting the below error at jboss startup :

07:57:42,561 INFO [JDBCDataStore] Generated token 'authToken:F0D1A900-88E4-11E3-B34B-8ED0EAFF8EE4' for user: 'jbossesb/JBoss ESB User'
07:57:42,712 ERROR [AuthTokenTable] Data truncation: Incorrect datetime value: '' for column 'CREATED' at row 1
07:57:42,781 ERROR [GetAuthTokenFunction] com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'CREATED' at row 1
07:57:42,781 ERROR [RequestHandler] com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'CREATED' at row 1

Kindly suggest if this error can be resolved by some jboss configuration change or we should be looking on database side for it’s resolution.

This works fine when we are using MySQL 5.1.


texts: Different execution plans and joins using synonym or table

texts:
Edit:

We found the leading cause. If you join tables with the same name but in different schemas you can get this result. Same with joining two tables using the same alias (whyever this works at all).

We have a very strange issue using Oracle 12.1.0.2.

This is the query:

SELECT * FROM OWNER1.TABLE1 JOIN OWNER2.TABLE2 USING (COLUMN1, COLUMN2, COLUMN3, COLUMN4) WHERE COLUMN1=10 AND COLUMN2='string' AND COLUMN3='01.04.2018' ORDER BY COLUMN3;

This results in a cartesian product of the two tables, returning a wrong dataset and thousands of rows.

This is the same query, but this time using the public synonym OWNER1TABLE1 for the table OWNER1.TABLE1:

SELECT * FROM OWNER1TABLE1 JOIN OWNER2.TABLE2 USING (COLUMN1, COLUMN2, COLUMN3, COLUMN4) WHERE COLUMN1=10 AND COLUMN2='string' AND COLUMN3='01.04.2018' ORDER BY COLUMN3;

This results in the correct data being returned (only 16 rows).

Oracle is also using two quite different execution plans:

Using the table name:

PLAN_TABLE_OUTPUT
-------------------------------------------------------------
Plan hash value: 3737086414
-------------------------------------------------------------
| Id  | Operation                           | Name          |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |
|   1 |  MERGE JOIN CARTESIAN               |               |
|   2 |   PARTITION RANGE SINGLE            |               |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| OWNER2.TABLE2 |
|   4 |     INDEX RANGE SCAN                | O2.IDX        |
|   5 |   BUFFER SORT                       |               |
|   6 |    PARTITION RANGE ALL              |               |
|   7 |     TABLE ACCESS FULL               | OWNER1.TABLE1 |
-------------------------------------------------------------

Using the public synonym:

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 1141852644

---------------------------------------------------------------------
| Id  | Operation                                   | Name          |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |               |
|   1 |  HASH JOIN                                  |               |
|   2 |   PARTITION RANGE SINGLE                    |               |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| OWNER1.TABLE1 |
|   4 |     INDEX RANGE SCAN                        | OWNER1.PKY$T1 |
|   5 |   PARTITION RANGE SINGLE                    |               |
|   6 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| OWNER2.TABLE2 |
|   7 |     INDEX RANGE SCAN                        | O2.IDX        |
---------------------------------------------------------------------

The synonym is defined like this:

CREATE public synonym OWNER1TABLE1 FOR OWNER1.TABLE1;

And maybe the strangest thing is, if we define an alias (e.g. T1) for OWNER1.TABLE1 in the query like

SELECT * FROM OWNER1.TABLE1 T1 JOIN OWNER2.TABLE2 USING (COLUMN1, COLUMN2, COLUMN3, COLUMN4) WHERE COLUMN1=10 AND COLUMN2='string' AND COLUMN3='01.04.2018' ORDER BY COLUMN3;

...it returns the correct data, too.

Anyone knows about a bug or something? Can't really be expected behaviour, can it?


texts:
Is there any relationship between the OLEDB Wait Type and functions? Trying to troubleshoot why I would have 77% of my waits as OLEDB wait type when we have no linked servers. I have a vendor database with a high number of both Table-valued functions and Scalar-valued functions and was curious if they could at all be related to the OLEDB wait type.

Most of the documentation I have found points to Linked Server queries for this wait type, but that's not the case for us.


texts: Improving statistics on a large "WHERE IN" SQL Server query

texts:
Currently, I'm trying to run the following example query:

SELECT [DATA1], [DATA2] FROM TABLE WHERE
    [DIMENSION0] IN (1, 5, ... (possibly 10s of numbers)) AND
    [DIMENSION1] IN (5) AND
    [DIMENSION2] IN (10) AND
    [DIMENSION3] IN (48) AND
    [DIMENSION4] IN (1) AND
    [DIMENSION5] IN (1) AND
    [DIMENSION6] IN (8) AND
    [DIMENSION7] IN (1) AND
    [DIMENSION8] IN (52) AND
    [DIMENSION9] IN (1, 10, ... (possibly 100s of numbers)) AND
    [DIMENSION10] IN (1, 235, ... (possibly 1000s of numbers)) AND
    [DIMENSION11] IN (1)

The table looks like the following;

D = Dimension

[D0] [D1] [D2] [D3] [D4] [D5] [D6] [D7] [D8] [D9] [D10] [D11] [DATA1] [DATA2]

Which contains a clustered index along all the dimensions, and could contain millions of records.

When I run this query through SSMS, I get the following query plan:

Here, it wildly overestimates the number of records it is looking for, which I believe is the reason for it running so slowly.

I've updated the statistics, but that wasn't the problem, so I'm left with it being an issue with the query.

I've also been able to improve the speed of the query by forcing SQL to run in parallel by using:

OPTION(QUERYTRACEON 8649)

This produces the following execution plan:

This is faster, but it is still overestimating the number of rows.

I was hoping that someone might be able to help me understand why this estimation is so high, and how I could possibly reduce it.

Clustered index definition:

/****** Object:  Index [ClusteredIndex-20180726-083210]    Script Date:
26/07/2018 09:47:58 ******/
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20180726-083210] ON
[dbo].[TABLE]
(
[DIMENSION0] ASC,
[DIMENSION1] ASC,
[DIMENSION2] ASC,
[DIMENSION3] ASC,
[DIMENSION4] ASC,
[DIMENSION5] ASC,
[DIMENSION6] ASC,
[DIMENSION7] ASC,
[DIMENSION8] ASC,
[DIMENSION9] ASC,
[DIMENSION10] ASC,
[DIMENSION11] ASC,
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS =
ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I perform insert, updates and deletes to the table.

I can't use a Clustered Columnstore Index, as the Data2 column is varbinary(max). I tried using a non-clustered version, but the query plan just used the clustered index.

I did update statistics with FULLSCAN. There are some Dimensions that are hit more than others. I have previously experimented with the order of the Dimensions in the Clustered Index, but it still overestimates the number of rows.

Full query with plan: https://www.brentozar.com/pastetheplan/?id=HyLHtXDVX


texts:
This is not exactly a question, more of a survey. Here at BlackBean we have a time series aggregation and analysis platform built on top of MySQL, where each client have your own database, and each metric has it's own table, like this:

CREATE TABLE IF NOT EXISTS `ts_metric_x` (
  time INTEGER(20) UNSIGNED NOT NULL DEFAULT '0',
  value DECIMAL(20,4) NOT NULL DEFAULT '0.0000',
  PRIMARY KEY (time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Currently we have about 90TB of client data stored within our database servers (2 production servers with about 16GB of RAM each) and so far the platform is running exceptionally well with execution times below 0.08 seconds.

Reading the logs we discovered that some pages (dashboards) were executing over 900 queries to aggregate and retrieve the metrics data. This high query count was caused because for each chart we first calculate all the required timestamp intervals, and then generate and execute a query for each metric at each interval, for example:

SELECT SUM(value)
FROM `ts_metric_x`
WHERE time>=1234567
AND time<=1234567

So metrics X aggregation X intervals = many queries. After a brainstorm session we though about the possibility to aggregate all the queries by metric, and the transform the above query in many subqueries and executed just once:

SELECT
  (SELECT SUM(value) FROM `ts_metric_x`
   WHERE time>=1234567 AND time<=1234567) AS 1234567,
  (SELECT SUM(value) FROM `ts_metric_x`
   WHERE time>=2345678 AND time<=2345678) AS 1234567,
  (SELECT SUM(value) FROM `ts_metric_x`
   WHERE time>=3456789 AND time<=3456789) AS 3456789,
  (SELECT SUM(value) FROM `ts_metric_x`
   WHERE time>=4567890 AND time<=4567890) AS 4567890

We deployed this solution to our heavier clientes, and the results are amazing. We managed to lower our execution times below 0.02, and the dashboards that were executing around 900+ queries are now generating 50 queries! The MySQL servers have the same load footprint so far.

My question is, does anybody have experience with this kind of data set or scenario? Do we have to worry about any gotchas regarding subqueries?

Cheers,
Bruno

Update
The execution times I've talked about was the page generation/execution, not each query execution. Sorry


texts:
I am running this query to do massive deletion in my database.
I am looking at Batchrequests/sec and it is quite low (like 15% of what my server can handle)
Is there any way to rewrite this query to do more batchrequest/sec?

delete from Position
Where MainFirmID = 26 and BatchID in (
       select BatchId from MyDB..Batch Where FirmId = 26
              and PeriodId <= 110 and PeriodId > 90
)

Thanks for your help.

RM


texts: Is it possible to have a unique key condition based on another value?

texts:
I have a table with the following columns:

ID, STRING_VALUE, ENUM_VALUE, CATEGORY_IDENTIFIER

I'd like to create a condition that won't allow two identical ENUM_VALUEs, but only if their ID is also the same.

For example:

1  BLUE    4  6   --> Good
2  BLUE    4  6   --> Good
3  BLUE    4  6   --> BAD
3  GREEN   4  6   --> BAD

Is there any way to create such a constraint?


texts: Will a clustered index improve performance of updates performed sequentially on that clustered index?

texts:
I know indexes reduce data modification performance, but I have a task where (almost) all updates are done on items in a sequential order. Would a clustered index improve or reduce performance in updating these rows?

The clustered index would be on column id where id is an IDENTITY column. id is thus never changed and will be sequential (in addition rows should never be deleted).
This is the format of my update statements:

UPDATE [table] SET value = 1
WHERE  id IN (1,2,3,4...)

The values are not necessarily continous however. A sequence of IN (1,2,4,5) is also possible if id=3 is not set to be updated, but they will always be in order.

When all updates are in sequential order, will a clustered index improve or reduce performance?


texts: Why COUNT(*) on linked server is inaccurate?

texts:
I have two SQL Servers, separate hardware boxes.
Server A:
 Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0 (X64)
    Sep 23 2016 15:49:43
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor).

Server B:
Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)
    Aug 15 2017 10:23:29
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

I recently deleted all the rows in one table from Server A and copied the rows from Server B using the import wizard.

When I execute these COUNT(*) queries from Server A, I get the identical result for each query, 200,703 rows.

select count(*) from dbo.thisTable

select count(*) from [Server B].[mydb].[dbo].[thisTable]

But if I exec the second COUNT(*)  on Server B, it tells me I have 196,420 rows.

The copy wizard threw no errors.  Any idea what happened?


texts: MongoDB 3.4 Server not starting with bind error following a CentOS 7.4 security update

texts:
MongoDB 3.4 server is failing to start following a CentOS security update and reboot.

No configuration changes made. The previous update and reboot a couple of days earlier did not cause any problems. Could the CentOS update be the reason for the problem?

Many thanks, any thoughts are much appreciated!

Error messages in mongod.log

E NETWORK  [initandlisten] listen(): bind() failed Cannot assign requested address for socket: xxx.xxx.xxx.xxx:27017
E NETWORK  [initandlisten] Failed to set up sockets during startup.
E STORAGE  [initandlisten] Failed to set up listener: InternalError: Failed to set up sockets

The CentOS upgrade installed before the reboot

Installed:
  kernel.x86_64 0:3.10.0-693.17.1.el7

Updated:
  binutils.x86_64 0:2.25.1-32.base.el7_4.2                  device-mapper-persistent-data.x86_64 0:0.7.0-0.1.rc6.el7_4.1
  dhclient.x86_64 12:4.2.5-58.el7.centos.1                  dhcp-common.x86_64 12:4.2.5-58.el7.centos.1
  dhcp-libs.x86_64 12:4.2.5-58.el7.centos.1                 initscripts.x86_64 0:9.49.39-1.el7_4.1
  kernel-headers.x86_64 0:3.10.0-693.17.1.el7               kernel-tools.x86_64 0:3.10.0-693.17.1.el7
  kernel-tools-libs.x86_64 0:3.10.0-693.17.1.el7            kmod.x86_64 0:20-15.el7_4.7
  kmod-libs.x86_64 0:20-15.el7_4.7                          kpartx.x86_64 0:0.4.9-111.el7_4.2
  libdb.x86_64 0:5.3.21-21.el7_4                            libdb-utils.x86_64 0:5.3.21-21.el7_4
  libgudev1.x86_64 0:219-42.el7_4.6                         net-snmp-libs.x86_64 1:5.7.2-28.el7_4.1
  systemd.x86_64 0:219-42.el7_4.6                           systemd-libs.x86_64 0:219-42.el7_4.6
  systemd-sysv.x86_64 0:219-42.el7_4.6                      tuned.noarch 0:2.8.0-5.el7_4.2


texts: Which query performs better when searching a row where a column has maximum?

texts:
Consider a simple table for example purposes:

person(name varchar, age int)

I have a task to retrieve the oldest person. Which query is better in general for such problems?

Query 1: criteria with subquery for maximum value

SELECT p.* FROM person p WHERE p.age = (SELECT MAX(age) FROM person);

Query 2: ordering and selecting first row

SELECT p.* FROM person ORDER BY age DESC LIMIT 1;

I'm currently using PostgreSQL, but the question is I think platform independent.


texts: Migrate SQL Server - server name in stored procedures references old server

texts:
I will migrate an SQL server from 2000 to 2008 R2. It contains two instances. I have many stored procedures that are referencing the instance names like:

select ... from [old_server\instance1].db1.dbo.table1

What is the best way to deal with these stored procedures?

Modify all stored procedures and change these occurrences
from [old_server\instance1].db1.dbo.table1 to [new_server\instance1].db1.dbo.table1
Do not change the stored procedures, but create a reference from the old server to the new one using sp_setnetname
Rename the new server after the migration to keep the old name


texts: Single database schema or multiple schemas?

texts:
I'm designing a website for an annual event. The events are similar but mostly independent year-to-year. I see 2 possible designs:

A single schema with most tables having an EventID column to tag data to specific year. Possibly have views for each year.
Multiple schema one per event. The events are independent, so it would be plausible to simply copy a schema and purge old data for each year.

I'd have multiple schemas for faster performance, ease of change, and programming simplicity, but I also feel that a single schema has its advantages such as database simplicity, uniformity, and easier access to old data (not common).

Which approach is preferable?
Any real world experiences on this?

Thanks.


texts: EAV vs JSON/XML column approach to store complex user form data in a database?

texts:
We are working on an internal tool which will essentially be a complex, power-user oriented form app. It is not a dynamic field app - users can't define their own fields.
Form field types: The form will contain a mix of the following form field types

Y/N questions (i.e. radio buttons)
Single-select dropdowns
Table mapping interface (i.e. map columns in table A to columns in table B)
Multi-select dropdowns (some of which are populated by the columns of table B)

Database: I briefly considered NoSQL approaches but would prefer this in Azure SQL because the team is very comfortable with relational databases and we will never have issues with data size for this app. Open to revisit this if people feel strongly.
Options: from what I've read I have the following options (excluding NoSQL tools)

Go full relational using an EAV-style approach
Use a JSON/XML data column in SQL to store the form field data

Considerations

Development time
Relational data integrity (i.e. a user deletes a column from Table B that is used in a multi-select)
Versioning - the user form is likely to change over time, although slowly and under developer control
Ease of running migrations, particularly more complex ones that require changes to existing user-entered data as opposed to just schema. An example: combining two yes/no questions into one and needing to update the data accordingly

I have had a hard time finding good resources online to compare pros and cons in these cases. Could anyone here please point me to good articles or information?


texts: Managing Permissions on a server with new DBs being added

texts:
I'm currently dealing with a database where multiple databases will be added in the future. Right now, I'm working on creating some users with specific permissions and I'm struggling with how to ensure that they can also cover the databases that aren't yet added. Complicating things, most of these permissions are needed only databases ending in a specific suffix.

For example, a user needs to be created that can add assemblies to all the current and future databases that end in ABC.

Is there a way to make this easier to manage, or will it involve always ensuring after a new set of DBs are added that the user has access to the ones ending in ABC?


texts: Error with Oracle trigger, invalid identifier

texts:
I have the following DML statement that is working perfectly, i have tried to make it into a trigger but it isn't working.

DML statement

UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL =
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0) * NVL(R.IN_FLIGHT_SEATS_NO,0) +
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.OUT_FLIGHT_ID), 0)* NVL(R.OUT_FLIGHT_SEATS_NO,0) +
   NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = R.ACC_ID), 0);

Trigger statement

CREATE OR REPLACE TRIGGER HR_SUBTOTAL
BEFORE INSERT OR UPDATE ON HOLIDAY_RESERVATION
FOR EACH ROW
BEGIN
   SELECT
      NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = :NEW.IN_FLIGHT_ID), 0) * NVL(R.IN_FLIGHT_SEATS_NO,0) +
      NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = :NEW.OUT_FLIGHT_ID), 0) * NVL(R.OUT_FLIGHT_SEATS_NO,0) +
      NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = :NEW.ACC_ID), 0)
      INTO :NEW.SUBTOTAL
   FROM DUAL;
END;

Errors for TRIGGER HR_SUBTOTAL:

LINE/COL ERROR
-------- ----------------------------------------------------------------
2/4      PL/SQL: SQL Statement ignored
4/92     PL/SQL: ORA-00904: "R"."OUT_FLIGHT_SEATS_NO": invalid identifier

The error has to be in the * multiplying bit.


texts: How many databases does a sybase instance have?

texts:
I'm a newbie to sybase and could not find the clear answers on Sybase tutorials/docs

How many databases can one Sybase instance have? Oracle has one
instance which corresponds to only one database, is it the same with
Sybase ?
What is a master database, is it a db that holds metadata? Does each
instance have one master database ?
What is a user database ? Is it similar to a tenant db in sap hana ?


texts:
I'm executing below query, but it keeps on executing.

In place of some time I have a time value.

select distinct(col1)
from table1
where col2 > 'some time'
  and col1 not in
      (select distinct(col1)
       from table2
       where col2 not like '%XYZ%');


texts: MySQL Error Reading Communication Packets

texts:
In MySQL error logs, I see these quite few a warnings like these:

120611 16:12:30 [Warning] Aborted connection 2619503 to db: 'db_name' user: 'user_name' host: 'webapp_hostname' (Got an error reading communication packets)

Haven't noticed any loss of data per se, so I am wondering what this warning means, or what causes it, and if how one might address the issue causing these. This is on RHEL 6.1 and MySQL Enterprise 5.5.


texts: Explaining about SQL Server query Execution Plans and understanding Clustered and Nonclustered Indexes

texts:
I'm Christine and I'm a newbie. I would like to know if someone could help me clarify what I'm drilling down this execution plan as below :

Here's query generated from SQL server that is accompanied with this execution plan (with 3 tables) and index as follows :

How many types of indexes in execution plan are there? Currently I can see this execution plan with 3 types of indexes aforementioned that consist of : Index Scan, Clustered Index Scan and Clustered Index Seek
Which table has the best performance?
Which table needs applying index?
Which table needs requerying to increase performance for database?

Thanks in advance for your assistance in this matter.

Rgds,

Christine


texts: Is it necessary to create primary and foreign keys in order to use tpc-h benchmark?

texts: I'm doing some tests with tpc-h benchmark. The tpch-h benchmark simulates a data warehouse environment. The most important things here are the queries against the data warehouse. Therefore, does it make sense to create primary and foreign keys for this benchmark? I read some articles that do that, but I don't understand why it's necessary.


texts: Validation of Partitioned table Performance

texts:
We are testing partitioning on a table which contains a datetime column (DateColumn).

Partition function I am using is:

-- 138623  +  16774 records
CREATE PARTITION FUNCTION pf_TableName_DateColumn (datetime)
AS RANGE LEFT
FOR VALUES ('20160101', '99991231');

How do I verify that partitioning the table is improving query performance?
There is one Clustered index on PARTITION SCHEME, do remaining non clustered indexes get statistics of partition?

I am testing it by running the same stored procedure with date parameters on two databases (real and replica). One contains a partitioned table (two partitions) whereas the other is simple. Profiler doesn't show any difference in CPU, Duration, Rows, or Reads.


texts:
I'm trying to list all weeks in a month, (e.g., 2015-02-01 to 2015-02-28) in the following format:

Tried using WEEK e.g., SELECT WEEK(2015-02-01) - WEEK(2015-02-28); but this just gave me the number of numbers in the month - 4.

What is the proper MYSQL statement to achieve this


texts:
I deployed a new VM Oracle machine using a copy of my existing Oracle VM. As a result, the new VM inherited the same host name.  This is redhat linux 6.5, Oracle11g. I changed hostname everywhere as far as I know. However, when I start the listener, it still points to old host name!

I can not find listener.ora under the usual $../network/admin.  I created one but still it ignores it.

Any suggestions?

Thanks in advance


texts:
I have a feeling the answer is "it depends on...", but I'm wondering if there's a concrete answer.
In Entity Framework, you build a query using C# code, and the framework converts to SQL and sends to the server to pull down the data. Suppose I want to retrieve a record from three tables. I've got at least three choices:

Use straight SQL (manual ADO.NET), send three SELECT statements in the same command, and map the results one resultset at a time with a DataReader. This is clearly the best approach from the database perspective, but it's the most work since I wouldn't be able to use the Entity Framework approach.

Send three separate commands from Entity Framework - this requires three roundtrips to the database server:

Person person1 = context.Persons.First(p => p.PersonID == 1);
Car car1 = context.Cars.First(c => c.CarID == 1);
House house1 = context.Houses.First(h => h.HouseID == 1);
// translates to the following SQL, one roundtrip at a time:
SELECT TOP(1) [p].[PersonID], [p].[PersonName] FROM [Person] AS [p] WHERE [p].[PersonID] = 1
SELECT TOP(1) [c].[CarID], [c].[CarName] FROM [Car] AS [c] WHERE [c].[CarID] = 1
SELECT TOP(1) [h].[HouseID], [h].[HouseName] FROM [House] AS [h] WHERE [h].[HouseID] = 1

Make one big ugly statement which still uses Entity Framework with a single command and a single statement:

var query = (from p in context.Persons.Where(p => p.PersonID == 1)
             from c in context.Cars.Where(c => c.CarID == 1)
             from h in context.Houses.Where(h => h.HouseID == 1)
             select new { p, c, h }).First();
Person person2 = query.p;
Car car2 = query.c;
House house2 = query.h;
// which makes the following SQL:
SELECT TOP(1) [p].[PersonID], [p].[PersonName], [t].[CarID], [t].[CarName], [t0].[HouseID], [t0].[HouseName]
FROM [Person] AS [p]
CROSS JOIN (
    SELECT [c].[CarID], [c].[CarName] FROM [Car] AS [c] WHERE [c].[CarID] = 1
) AS [t]
CROSS JOIN (
    SELECT [h].[HouseID], [h].[HouseName] FROM [House] AS [h] WHERE [h].[HouseID] = 1
) AS [t0]
WHERE [p].[PersonID] = 1

The cross joins end up just flattening out the result since there's only one record returned per table, and the right indexes fire, so I would think the database would be happy with it - fewer roundtrips but the same amount of data returned.
The code in #3 is uglier, but that's not what I'm asking - I'm just asking from a performance perspective. Do the cross joins in the third example cause any potential issues? Maybe locking problems or extra CPU or memory that wouldn't otherwise be needed by using individual commands?


texts: Do I need to have enterprise server in order to have multiple data sources in SSRS

texts:
I am currently running a single server setup of SSRS on MSSQL Server 2008 R2 Standard Edition and would like to know if I can have multiple data sources in this type of setup.

The reason I ask this is that I currently have a different SSRS setup (single server) where the primary reporting server sits in one state (physical, as in cross country) it then pulls data from several different data sources, some local, some sitting in a completely different state, it then renders the data and pushes it all to the client locations which are in a completely different state then the SSRS or SQL server data sources (Think a very large triangle with SSRS at one corner, SQL at another, and Clients at the third).

Our goal is to move to an Enterprise setup eventually, but as a stop gap I would like to move the SSRS and all SQL connections to the same physical data center so as to reduce bottlenecks. My problem is that I have been told from local sources that SSRS needs to have an Enterprise edition version of SQL Server in order to maintain multiple Data Sources. As it is not possible (nor feasible) to have all data sitting on one database (XD), this would require a further license upgrade on this server which is likely not to be approved due to a further GDW project on the horizon.

Cheers


texts: Recommendations on replicating an RDS PostgreSQL instance?

texts:
At my company, we are looking for alternatives to replicate a PostgreSQL instance into another database. In our scenario, we have a production PostgreSQL 9.5 database server, with new entries coming in all the time, and we must sync them to a replica server.

But we are facing some challenges:

We need to be able to write to the replica database, as we want to include data from other sources as well. We've tried to use the AWS's own PostgreSQL, but apparently it doesn't allow writing to the replica.
It needs to replicate PostgreSQL special types, such as computed indices, enum types and JSON columns. Some of the tools we've researched don't do well with these types.
Also, it's necessary this tool operates without super user permissions, as the database is hosted in AWS RDS, and it doesn't give you super user permissions.

We've researched several solutions, and even wrote our own, but we are having consistency problems in the replica (i.e. some failed patches), as well as some ALTER TABLEs that failed to be replicated. We still haven't figured out a way to isolate and correct these problems in the source code.

Given all the situation, we are researching alternatives and would love to hear from people who had a similar problem.


texts: Error Message while executing command in Arbiter for log rotation

texts: While running the command in the shell db.adminCommand( { logRotate : 1 } ) of arbiter it showing an error message as not authorized to run this command but in primary and secondary of replicaset its working. So what's the solution for this version is 3.4.3.


texts: I'm working in redesign an application. This application has a MySQL database and has approximately 50 tables (there are no relations , there are not standars, etc). We want to migrate to Postgresql with some tables, some tables will not migrate, some will be residing totally and so more changes. My question is : Which techniques are recommend for this task ? Are there any tools for these job? and How can I do these in a efficient way?


texts:
I have a query that is pulling data from 10 tables and a sub-query. One of the selects is for a location and I am using a case expression to clean up the data on output. The field it is pulling the data from is varchar, and the data can be values such as: USA800, admin, ccc-ulw, ccc-ury, 002-Carson, 066-Nellis BX, 042-Junction City (there are more).

I want the case express to report back a specific value if it one of the locations that don't start with a number, but if it is a number, I want it to return just the number, as an int (dropping the 0's).

Here is the query as I have it now (the entire SQL may not be needed, but I figured it would help context):

    select
    ct.id as CaseID
    ,c.first_name as FirstName
    ,c.last_name as LastName
    ,c.id as CustomerID
    ,SUBSTRING(sdu.User_ID,CHARINDEX('-',sdu.User_ID)+1,(((LEN(sdu.User_ID))-CHARINDEX('-', REVERSE(sdu.User_ID)))-CHARINDEX('-',sdu.User_ID))) as [Username from Navigator]
    ,case sdu.Application_Name when 'Navigator' then sn.[RESP CODE] else '' end as [Responsibility Code from Navigator]
    ,case sdu.Application_Name when 'Navigator' then RIGHT(sn.[EMUL STATION], 4) else '' end as [CStat From Navigator]
    ,ce02.id as JobCode
    ,case l.id
        when 'USA800' then 'USA800'
        when 'admin' then 'Admin'
        when 'ccc-ulw' then 'CCC-Lawrence'
        when 'ccc-ury' then 'CCC-Raytown'
        when 'ccc-usj' then 'CCC-St Joseph'
        when 'ccc-uwf' then 'CCC-Wichita Falls'
        else cast(SUBSTRING(l.id, 0, charindex('-', l.id, 0)) as int)
    end as location
    ,l.id
    ,c2.first_name as SubmittedByFirstName
    ,c2.last_name as SubmittedByLastName
    ,sn.INST as Institution
    ,case sdupc.Application_Name when 'PartnerCare' then sdupc.User_ID else '' end as [Partnercare Username]
from Case_Table ct
    left join Case_Type t on t.case_type_pk = ct.case_type_pk
    left join Case_Category cc on cc.case_category_pk = ct.case_category_pk
    left join (select max(cas.case_pk) as case_pk, customer_pk from Case_Table cas join Case_Type cat on cas.case_type_pk=cat.case_type_pk where cat.id = 'ASR' group by cas.customer_pk) ce01 on ce01.case_pk = ct.case_pk
    left join Custom_Entity02 ce02 on ce02.custom_entity02_pk = ct.Virtual_JobCode
    left join Customer c on ce01.customer_pk = c.customer_pk
    left join Location l on l.location_pk = c.location_pk
    right join Customer c2 on c2.customer_pk = ct.installed_by_customer_pk
    left join Prod.dbo.Staging_DataFeed_Users sdu on sdu.User_Name = c.description and sdu.application_name = 'Navigator'
    left join Prod.dbo.Staging_DataFeed_Users sdupc on sdupc.User_Name = c.description and sdupc.application_name = 'PartnerCare'
    left join Prod.dbo.Staging_Navigator sn on sn.NAME = c.description and sn.NAME = sdu.User_Name
where t.id='ASR'

When running the query, I get a conversion failed error when converting the varchar value to data type int.

Either I am missing something, or there is a better way to do it and I am open to ideas.

The customer is expecting integer in this case (they want it to look like an integer without the leading zeros).

The results of this query are used in a foxtrot script for de-provisioning users from applications and those numbers represent branches in our bank system.  The leading zeroes cause issues.  If the result is a string, it is ignored.


texts: Write Oracle proc/function to create dynamic views with changing column headers

texts:
I had similar requirement earlier but now i have been given a clear approach that needs to be implemented.
I need to write a procedure(preferably)/function, which would on basis of app_id passed as parameter, change the column headers for corresponding values pivoted in the dynamic view.
The entire sample data and structure is available here:
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=63c08123fbf2fb13de81df0bff360c0b
The table with column headers is DATA_HEADER.

The table with value is DATA_VALUE.

The column headers and values need to be pivoted in order.
Both tables have app_id on common. So for app_id=1, labels from DATA_HEADER would be used against values in DATA_VALUE. However, the values change on basis of PID only, so they would remain constant, only headers would change per app id.
When app_id is passed in proc/funct, the expected view should be:

So basically, the headers change for each app_id, and the max number of column headers will be 20. So the number or name shall vary as in the table DATA_HEADER.
The values are uniquely identified on the basis of pid.
The order of column headers would be as per the seq column in DATA_HEADER.
Similarly the order of values would be as per seq column in data value, so the sequence must be followed and pivoted accordingly.
P.S. Application at the end is Oracle apex from where the proc/function would be called.
Oracle Version: 12.1


texts: SQL Server 2016 with high tempdb usage and low RAM usage

texts:
I've got a small instance of SQL Server Standard that I'm running HammerDB TPC-H (1 GB) benchmarks on. It's got some configuration issue though where it's writing everything into tempdb on disk, and only tempdb which seems to be bottlnecking me as my physical storage is pretty slow:

The database by itself is about 3.5 GB, and the server has 16 GB RAM.

My understanding is that tempdb is only used when the server is under memory pressure, but it never even hits 30% usage. Physical_memory_in_use_kb is never much more than 3 GB, though it steadily grows over time. The VMware host is not having any obvious trouble either.

sp_blitz results warn about high CPU usage (~75-85%) and not much else.

This is an almost default installation of SQL Server Standard in a cluster - Is this behavior of writing to tempdb normal in the case of these longer-running benchmark queries? If not, where can I start troubleshooting?

In response to Comments:

Max memory is set to 14336 GB in Server Properties > Memory
Relevant hammerdb code with tcph queries: https://pastebin.com/AwJ000Qp
Example planfile (query #18 above): https://www.brentozar.com/pastetheplan/?id=B1CkC9kE4


texts: MySQL: To use MYISAM or INNODB engine? (plot twist enclosed)

texts:
We use INNODB for all of our tables, but I have one special table, a 100+ GB reference table (only read-queries against it).

We are loading changes to this table in a nightly batch job using LOAD DATA INFILE.

MyISAM is more compact and efficient for a reference table. But this would be the ONLY MyISAM table we use.

The workload on the rest of the tables is typical web application except for 1 table which has a heavy write load and normal read load.

Question 1 - Is it worth the tradeoff of dedicating memory to the MyISAM cache for just this one table, when I know I need a reasonably amount of memory dedicated to the InnoDB cache for the other workload usecases?
Question 2 - When I run LOAD DATA INFILE 'from_nightly_batch' REPLACE INTO TABLE mytable, will MyISAM lock the table for reads? Will InnoDB?  e.g. can I use the reference table while I do the nightly update in either engine?


texts: Spike in AWS RDS Oldest Replication Slot lag

texts:
Does anyone know about this "Oldest Replication Slot Lag" in AWS RDS? What does it represent?

I have a PostgreSQL instance on RDS (with Logical Replication switched on) for transferring data from PostgreSQL DB to S3 bucket (in CSV form) using AWS Data Migration Service. The graph of "Oldest Replication Slot Lag" is too high on this instance. and also Transaction Log Disk Space goes up too.

Any help?


texts: Open Source Business Intelligence/DWH solutions

texts:
I wonder that this question hasn't already been asked. Google only has very few results for me that don't show a high quality tool

What are some Open Source (also free is ok) solutions for Data Warehouses and more specifically Business Intelligence Tools? What are your experiences with them. I have had a course in my masters programm and we worked with MS Business Intelligence and MSSQL as Data Warehouse storage. Now I want to get more into this topic with tools that are "open".

Are there any compareable tools for Business Intelligence (mostly database independent) and do you have any experience with them?

edit With Marian's comment to Stephanie's answer, I see that I have formulated the question wrong. I am aware that DWH are just "reporting-optimized" databases, Stephanie's explanation was very clear on that. I am more interessted how to get the data into such optimised forms with WHAT kind of BI software/tools/other techniques.


texts: Cannot connect to Oracle 19 instance running in a container under Ubuntu 20.04 via WSL2 on Docker for Windows

texts:
Here is my docker-compose.yml file:
version: '3.7'

services:

  database:
    image: scgain.azurecr.io/databases/${IMAGE_NAME}
    environment:
      - ORACLE_SID=${SID}
      - ORACLE_PDB=${PDB}
      - ORACLE_PWD=somepassword
      - ORACLE_CHARACTERSET=WE8MSWIN1252
    volumes:
      - ${ORACLE_HOST_DIR}oradata:/opt/oracle/oradata # persistent oracle database data
      - ${ORACLE_HOST_DIR}data-bridge:/data-bridge    # share data with the running container
    ports:
      - 1521:1521
      - 8080:8080
      - 5500:5500

and here the corresponding .env file:
IMAGE_NAME=oracle-19-e
SID=ORCL19CDB
PDB=ORCL19PDB1
ORACLE_HOST_DIR=D:\src\oracle\

When I run this container in Windows via PowerShell, I can connect to the Oracle instance just fine via SQL Developer (through hostname localhost and port 1521) and with my app using this connection string:
Host=localhost;Port=1521;User ID=someuser;Password=somepassword;Service Name=ORCL19PDB1;Direct=true

When I run it instead in WSL2 (Ubuntu 20,04) I can browse the mapped volumes inside the container. I can connect to the instance inside the container via sqlplus and see all the schemas and tables, but trying to connect to it through SQL Developer gives me a Code 17002 error (unable to establish connection). Also, my app throws an analogous exception that it cannot find the database.
Here is the .env file for Linux:
IMAGE_NAME=oracle-19-e
SID=ORCL19CDB
PDB=ORCL19PDB1
ORACLE_HOST_DIR=/mnt/d/src/oracle/

I checked with lsnrctl status that the TNS listener is running inside the container.
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 03-NOV-2020 20:31:27

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                03-NOV-2020 19:50:01
Uptime                    0 days 0 hr. 41 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/32c1b8c48d42/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=32c1b8c48d42)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/ORCL19CDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ORCL19CDB" has 1 instance(s).
  Instance "ORCL19CDB", status READY, has 1 handler(s) for this service...
Service "ORCL19CDBXDB" has 1 instance(s).
  Instance "ORCL19CDB", status READY, has 1 handler(s) for this service...
Service "ab0e9cc3a7c10befe053020016acb4c7" has 1 instance(s).
  Instance "ORCL19CDB", status READY, has 1 handler(s) for this service...
Service "orcl19pdb1" has 1 instance(s).
  Instance "ORCL19CDB", status READY, has 1 handler(s) for this service...
The command completed successfully

What do I need to do to connect to the db outside of its container when it is running in WSL2?


texts: Oracle SQL: Outer Join Not Working As Expected

texts:
I am joining two tables, T_ADMSTU_PROG_ENROLL (renamed prog_enroll) and T_ADMGRAD_STUDENT (renamed grad). I would expect the result to have the following columns:

TERM_CODE, STU_INST_UID, MAJOR_DESC, MAJOR_CIP, B_GRADUATION_TERM

keyed on prog_enroll.STU_INST_UID = grad.STU_INST_UID and prog.enroll TERM_CODE = B_GRADUATION_TERM and include all rows from both tables. I am, however, missing rows from T_ADMGRAD_STUDENT where the value B_GRADUATION_TERM is not in the TERM_CODE. What am I doing wrong?

SELECT prog_enroll.TERM_CODE, prog_enroll.STU_INST_UID, prog_enroll.MAJOR_DESC,prog_enroll.MAJOR_CIP, grad.B_GRADUATION_TERM FROM
(SELECT DISTINCT TERM_CODE, STU_INST_UID, LISTAGG(CIP_CODE,'+') WITHIN
GROUP(ORDER BY CIP_CODE) as MAJOR_CIP,
LISTAGG(CIP_DESCR,'+') WITHIN GROUP(ORDER BY CIP_CODE) as MAJOR_DESC
FROM
T_ADMSTU_PROG_ENROLL
WHERE
DEGREE_LEVEL_CODE = 'B' AND COLL_TYPE = 'MT'
GROUP BY TERM_CODE, STU_INST_UID) prog_enroll
FULL OUTER JOIN
(SELECT DISTINCT STU_INST_UID, B_GRADUATION_TERM
FROM
T_ADMGRAD_STUDENT
WHERE
STUDENT_ACADEMIC_LEVEL = 'US'
)  grad
ON
grad.STU_INST_UID = prog_enroll.STU_INST_UID
AND
grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE;


texts: Joining two fast subqueries is very slow

texts:
DB: MySQL 5.5.20 (WampServer)
OS: Win 7
HDD: Western Digital 3TB Caviar Green, 3.5", IntelliPower, 64MB, Sata3 (WD30EZRX)
Memory: 8GB
my.ini: http://pastie.org/private/s7l9aadyi0qhfl5bcrnnqq

I've got two tables:

CREATE TABLE `person_seen` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `person_id` int(11) NOT NULL,
 `role_id` tinyint(3) unsigned NOT NULL,
 `kind_id` tinyint(3) unsigned NOT NULL,
 `seen` smallint(5) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `person_id` (`role_id`,`kind_id`,`person_id`)
) ENGINE=InnoDB AUTO_INCREMENT=63501 DEFAULT CHARSET=utf8

CREATE TABLE `person_total` (
 `person_id` int(11) NOT NULL,
 `role_id` tinyint(3) unsigned NOT NULL,
 `kind_id` tinyint(3) unsigned NOT NULL,
 `total` smallint(5) unsigned NOT NULL,
 KEY `person_id` (`role_id`,`kind_id`,`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

person_seen has the number of films I've seen from each person_id for given role_id and kind_id. It only has entries where seen is > 0.

person_total has the total number of films by each person_id for given role_id and kind_id.

Roles are things like director, writer, actor, etc. Kinds are things like movie, tv movie, tv series, etc.

The user should be able to choose the exact combination of values they want to access.

One such query I'm trying to run is:

SELECT name.id, name.name, ds.is_favorite, ds.is_disliked,
    ds.is_watchlist, ps.seen, pt.total
FROM (
    SELECT person_id, SUM( seen ) AS seen
    FROM person_seen
    WHERE role_id = 8 AND kind_id IN ( 1, 3, 4, 5 )
    GROUP BY person_id
) ps
LEFT JOIN (
    SELECT person_id, SUM( total ) AS total
    FROM person_total
    WHERE role_id = 8 AND kind_id IN ( 1, 3, 4, 5 )
    GROUP BY person_id
) pt ON pt.person_id = ps.person_id
LEFT JOIN name ON name.id = ps.person_id
LEFT JOIN person_status ds ON ds.person_id = ps.person_id

You can see EXPLAIN of the query here: http://pastie.org/private/tv9cp9y6pfa44glzynuowa

The query means:

  Get a list of all directors (role_id=8) I've seen at least one film from (movie, tv movie, video movie, tv mini-series), their total number of films for those categories, their names, and if I have them in my favorites/dislikes/watchlist.

The slowness of the query is JOINing the two subqueries. By slow I mean no patience to wait for it to return anything.

I've tried creating VIEWs for those subqueries but JOINing them is still equally slow.

Running those subqueries separately is very fast though, below 0.1 seconds.


texts: I come from Oracle Database and now I am trying learn SQL Server and I don't undestand why may I viewer others databases, like the master for example, with a common user without privileges in this database?


texts: How do I restore old db table files on a new MySQL server?

texts:
I have MySQL db files *.frm, *.MYD and *.MYI. But my newly installed MySQL only support *.ibd files.
I throwed mySchema folder onto C:\ProgramData\MySQL\MySQL Server 8.0\Data and restarted MySQL service, it didn't work.


texts: running sql to insert so records and making sure am the only one on the db

texts:
I'm new to sql. I just want so reassurance my sql is correct before I deploy it onto the live server.  Firstly it's suppose to put the database am playing with into single user mode.

It then inserts the records and puts it back to multiuser mode. However it should roll everything back if it fails.

ALTER DATABASE MartinLive SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
BEGIN TRY
        INSERT INTO[dbo].[RuleSet]
           ([Id]
           ,[TypeId]
           ,[Description]
           ,[FileName]
           ,[RuleSetOrder])
     VALUES
           ('irl-appval',
           1,
           'CUstom field sale channel(XAGENTNAME)',
           'irl-appval.xbre',
           99
)

INSERT INTO [dbo].[Life_CompaniesRuleSet]
           ([Life_CompanyId]
           ,[RuleSetId])
     VALUES(
           6
           ,'irl-appval')
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
 ALTER DATABASE MartinLive SET MULTI_USER

is this the correct way of doing what i want to archive using Microsoft SQL
Server 2008 R2 (RTM) - 10.50.1600.1 (X64)


texts: Trouble with SQL Server authentication in SQL Server 2012

texts:
I am working with a SQL Server 2012 database. I am able to log into the server using Windows authentication. Although I have the SQL Server authentication username and password which is provided to me by the team, but authentication fails.

Is there anyway to find the password for the username : sa. Also, how to create new username and password without affecting the existing sa username.


texts: How to deal with equally growing fact/dimension tables in datawarehouse design?

texts:
I have a source data set with:

customer
customer_product_purchase
customer_support_plan_purchase
customer_support_request

All of them have a relationship such that a support request is raised against a plan and product purchase. And that a customer buys a support plan for a product (which the customer also buys).
In order to design a data warehouse schema for this, I was thinking of creating a single fact table, I thought of the following approaches:

Having a consolidated fact table with customer_product_purchase, customer_support_plan_purchase and customer_support_request into one as they have a few common attributes (and few uncommon ones which can be kept blank for others). As I believe they are at the same granularity (purchase of product/support plan, raising a request against a support plan). This would mean losing some specific information as to make it generic, like product warranty and support plan validity under the same name validity
Creating a fact table from customer_product_purchase and customer_support_plan_purchase which are inherently purchases, and can be kept together with some common and some uncommon attributes. The customer_support_request can be treated as separately.
Creating a fact table around customer_support_request as it ties up to both the other tables, which can be the dimensions. However, this will mean that the dimensions will also grow at the same rate as the fact (which I have read, is an indicator of bad design).

So how can I deal with such a situation where the support plan, service request and product purchase can grow by themselves individually, is it best to just keep all of them separate? But because they (all or two of them) have similar granularity, shouldn't they be consolidated?


texts: Find old linked servers on a restored copy of master/msdb db

texts:
We moved our dbs to a new server and the drives on the old server are still available, but the SQL service is offline and cannot be started.

I want to find the linked servers that were registered on the old server. I tried restoring the master db under a different name on another SQL server. That's all fine, but when I tried to run

select * from newmaster.sys.servers

it actually looks in the actual master db of that server instead of what I specified?

How do I view the list of linked servers in this case?


texts: SQL Server Threshold of Write per X when considering Indexes?

texts:
I am not entirely sure if this is even a question worth asking. However, I was curious as if there was some sort of standard, or specifically a metric, that some more seasoned DBA's use to gauge whether or not an index would be worth adding based on the general activity with the table. I mean anecdotally, yes we all understand tables that get written too frequently shouldn't have too many indexes and the more you have the bigger the issue is/can be. However, I was curious as to whether or not there is a generic standard which can be approach using some sort of formula?

I know through my research there's a lot of "it depends answer" because all environments are not the same, not too mention all infrastructures differ as well. I was hoping one of you smarter individuals would be so nice as to bestow some wisdom on me.


texts: Dynamic Table partitioning on a daily basis

texts:
I have a SQL Server database which contains two tables -- Acks and Logs.

These two tables are related in logic but not in a relational database way. Basically, every message that comes in gets saved in the Log table and, if our server acknowledges it, then that ack gets stored in the Ack table.

We are storing around 5 million Acks and 3 million Logs a day. I am trying to partition these two tables on a daily boundary so that we can easily remove older partitions from the table, and also improve query performance.

I haven't done table partitioning before, so I have been reading some on-line tutorials, however I am stuck on one thing. All the tutorials I have followed seem to manually add filegroups and manually add boundaries.

I want SQL Server somehow to do this daily, and this is what my question is about. I need it to create the new filegroups for the next day, every day at, say, 22:00. Then at 24:00 the inserts should start filling up the new day's partition.

Can anyone point me in the right direction on how to achieve this? Either a comprehensive tutorial or some good old advice will do as well.

My second question: can I somehow apply the same partition function to the two different tables?

They both have a datetime(2) column on which I want to partition, and the same rules
will apply.

How does that then fit in with my filegroups? Do I need a single filegroup for the day? Will each table have a file in that filegroup, or will both tables save to the same file in the filegroup?

Do I have to make a .mdf and .ldf for each filegroup? Or is there still one log file for the entire database?


texts: Dynamically changing tables in datasource in SSRS

texts:
I have an SSRS report that I have set up to query two tables, let's call them ABC_Audit and ABC_AuditDetail. I have now added XYZ_Audit and XYZ_AuditDetail to the same database, and want to be able to switch between querying the ABC... or XYZ... tables in the report. The tables are set up exactly the same, so the only part of the query that would need to be changed is the table name in the FROM ... clauses.

In my non-DBA, not-very-SSRS-knowledgeable eyes, there might be two ways to achieve this

either dynamically change the data source (have one data source for each set of tables to query),
or use a parameter in the SQL query to choose between the sets of tables to query, either of which is accessed using a drop-down on the report page.

Creating a separate report for each set of tables is not a viable option, as the number of sets will grow over time, and keeping all those reports up-to-date if/when it changes will be grueling.

Does anyone have any advice on how to achieve this, and which option would be the better option to pursue?


texts: pt-table-checksum - Diffs cannot be detected because no slaves were found (1)

texts:
I'm new to Percona tools. I am trying to use pt-table-checksum as follows:

pt-table-checksum h=localhost -u root -p xyz--replicate=percona.checksums --create-replicate-table --databases=mysql

But get the error.

Diffs cannot be detected because no slaves were found

Please read the --recursion-method documentation for information.

My.cnf

#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock
default-character-set=utf8

[mysqld]
datadir=/spacedb
socket=/tmp/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
init-connect='SET NAMES utf8'
character-set-server = utf8
max_connections = 300

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqladmin]
socket=/tmp/mysql.sock

[mysql]
socket=/tmp/mysql.sock
default-character-set=utf8

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = multi_admin
password   = user

[mysqld1]
socket     = /tmp/mysql.sock
port       = 3306
pid-file   = /var/run/mysqld/mysqld.pid
datadir    = /spacedb
language   = /usr/share/mysql/english
server-id  = 1
log-bin    = mysql-bin
binlog_format = mixed

# Expire_logs + MaxBinlogSize
expire_logs_days = 7
max_binlog_size = 100M

sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout=1

init-connect='SET NAMES utf8'
character-set-server = utf8
max_connections = 300

[mysqld2]
socket     = /tmp/mysql.sock2
port       = 3307
pid-file   = /var/run/mysqld/mysqld2.pid
datadir    = /repdb
language   = /usr/share/mysql/english
server-id  = 2
relay-log  = mysqld2-relay-bin

innodb_lock_wait_timeout=1
init-connect='SET NAMES utf8'
character-set-server = utf8

Do I have to use DSN? And how does it work?


texts: Can every SPARQL query be expressed as a GraphQL query?

texts:
The question is really formulated in the title :-)

I am working on an RDF project but I want to make it as easy as possible to interact with the API. Therefor GraphQL seems the most likely.

UPDATE:
Got an answer on Quora.


texts: Continuous replication between Oracle Database and Azure SQL Database

texts:
Background info: Our core ERP has its data on Oracle. Our own applications are Microsoft SQL Server based.

Almost every application reads data from this Oracle database and usually there is some sort of synchronisation where we copy the entire database.

When looking around there are numerous solutions. Most of these seem deprecated. (e.g. Oracle GoldenGate).

Our Oracle database is on-premise and we intend to use Azure SQL Database for our future applications.

What is currently the best way to set-up this synchronisation?

Master: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Slave: Azure SQL Database

I must have assumed that Oracle GoldenGate was deprecated since I've seen lots of old posts but hardly any recent ones. But this is another 'tool' to keep our data in sync. Are there other/better solutions?

Perhaps a linked server is an option in my case? Is that possible with Oracle on-prem and Azure SQL Database?

Is GoldenGate the best option if you want replication?


texts:
I'm used SQL Server 2014.
My big table partitioned by date column and each partition has own datafile.
I want delete oldest partition data and data file too.

How to do it?


texts: rman backup takes too long time on oracle 10g

texts:
I have a problem that my RMAN backup on our sl45 tape drive takes 2 days to backup 450GB database. The problem that the log shows is that the backup waits too long between finishing a certain task and starting another task.

Example:

Finished backup at 21-MAR-2019:19:28:01
Starting backup at 22-MAR-2019:08:33:21

These 2 tasks are in the same backup session. I don't know why it takes so long time between tasks.

Backup script:

connect target /;
run {
    allocate channel ch1 type  'sbt_tape' parms 'ENV=(OB_MEDIA_FAMILY=IMC_ONLINE_DB)' ;
    allocate channel ch2 type  'sbt_tape' parms 'ENV=(OB_MEDIA_FAMILY=IMC_ONLINE_DB)' ;
    sql'ALTER SYSTEM ARCHIVE LOG CURRENT';
    BACKUP INCREMENTAL LEVEL 0 DATABASE INCLUDE CURRENT CONTROLFILE PLUS ARCHIVELOG;
...
...

Does anybody have an idea why I might be encountering such issues?


texts:
I am trying to prepare a dataset for box chart which requires MAX, MIN, MEDIAN and quartiles grouped by years. This is my attempt, however I could not group it by years:
SELECT
    years,
    MAX(NET_MERIT) AS high_end,
    max(q3) as q3,
    max(MEDIAN) as median,
    max(q1) as q1,
    min(NET_MERIT) as low_end

    from(

    select Year(FA_BDATE) as years ,NET_MERIT,
    PERCENTILE_CONT(0.25) within group(order by NET_MERIT) over (partition by Year(FA_BDATE)) as q1,
    PERCENTILE_CONT(0.50) within group(order by NET_MERIT) over (partition by Year(FA_BDATE)) as MEDIAN,
    PERCENTILE_CONT(0.75) within group(order by NET_MERIT) over (partition by Year(FA_BDATE)) as q3
    from HerdAnalytics_tbl
    group by Year(FA_BDATE)
    order by Year(FA_BDATE)

    ) as sub
    order by years

The table is like this: db<>fiddle

FA_BDATE
NET_MERIT

01-01-2009
175.23

01-01-2008
185.23

01-01-2009
195.23

01-01-2010
165.23

And I have a small question: is PERCENTILE_CONT(0.50) is the median itself?


texts: SQL Developer auto trace does not show cascading deletes

texts:
I'm trying to understand the performance characteristics of SQL statement that does DELETE cascade on 4 linked tables.

When I do the auto tracing of the statement it only shows me the DELETE statement executed on the table. It does not show details of deletes done on other tables.

Why doesn't SQL Developer show cascades in tracing?


texts: BCNF decomposition step without functional dependencies

texts:
I am performing the BCNF decomposition for the relation R = (A, B, C, D, E), that has the set of functional dependencies AB -> C, CD -> A, C -> E, and C -> B.

After checking that the first dependency AB -> C violates the BCNF (by calculating the closure of AB), I split the original relation into two - R1 = (A, B, C) and R2 = (A, B, D, E).

At this point, the functional dependencies associated with R1 are AB -> C and C -> B, so I can see that R1 is in the BCNF. When it comes to R2, I cannot use any of the functional dependencies to verify if there are any violations to the BCNF, as all of them have the C attribute. How can I proceed if I do not have any functional dependencies to check?


texts: comparing left join and outer apply doing the same thing

texts:
I have an update trigger on a table that that insert rows in another database/table

the query look like

insert into otherdb.table
select manyfield
from inserted
  left join (select id,
                    timestamp,
                    row_number() over (partition by id order by timestamp desc) as rownum
             from sometable) as t1
      on inserted.id = t1.id and
         rownum = 1

sometable have over 7 millions rows and have proper indexing

while doing execution plan i can see that if there is 1 updated row, that windowing fuction (proper name used?) retrieve the whole table do to the rows numbering and get executed once, it is using index scan

same(it will retrieve once the 7 millions rows) if inserted have 20 rows or inserted have 50,000 rows

now the usage of that trigger is mostly 1 row update (we do have some odd case where it will be 20-100 rows and even thousands)

I refactored this query to look like

insert into otherdb.table
select manyfield
from inserted
  outer apply join (select top 1 id,
                    timestamp
                    from sometable
                    where inserted.id = sometable.id
                    order by timestamp desc) as t1

same result at the end but the execution plan change.

now it is using index seek and it will return 1 row per inserted row

when i do a huge update of 50,000 rows, the execution plan will tell me number of execution 50,000 instead of one when doing the left join version.

to resume everything;

left join: 1 big operation being reused by all inserted row

outer apply: 1 small operation being executed once per inserted row

my question is, at this point i don't know enough about execution plan to decide which kind of join i should keep, left join or outer apply?

this is being used by 500-1000 users at the same time and we have timeout error maybe related to this trigger, we currently use the left join query

EDIT

result of some actual execution plan, select on 55017 rows

left join:

estimated subtree cost: ~78
memory grant: ~156k
estimated number of rows: ~57k

outer apply:

estimated subtree cost: ~99
memory grant: ~163k
estimated number of rows: ~55k

hybrid(solution of Paparazzi but using innner join, for left join see above):

estimated subtree cost: ~87
memory grant: ~170k
estimated number of rows: ~56k

result of some actual execution plan, update on 55017 rows (trigger)

left join:

estimated subtree cost: ~401
memory grant: ~455k
estimated number of rows: ~225k

outer apply:

estimated subtree cost: ~136
memory grant: ~153k
estimated number of rows: ~52k

hybrid(solution of Paparazzi but using innner join, for left join see above):

estimated subtree cost: ~126
memory grant: ~156k
estimated number of rows: ~53k


texts: mssql revert changes made today without backup

texts:
A bug in an app made some changes now i need to rollback without a backup
Can i use the logfile for this ?
Is 'ROLLBACK' another option ?

INFO:
-My Recovery model is set to simple
-AutoCommit hasnt been set
-When i try to use the restore task, it tells me 'no backupset selected to be restored'


texts: MySQL: Why is this UPDATE query so incredibly slow?

texts:
So I have a landing table for my data called XMLImport, and a products table called ActiveProducts. When there are changes to a product in the XMLImport table I need it to update the ActiveProducts table, but only update the records which are needed and keep track of which  ones.

This is the code I am using to find changes :

SELECT DISTINCT sku FROM (
  SELECT name,size,colour,sku,quantity,price FROM ActiveProducts
  UNION ALL
  SELECT name,size,colour,sku,quantity,price FROM XMLImport
) tbl
GROUP BY name,size,colour,sku,quantity,price
HAVING count(*) = 1
ORDER BY name,size,colour,sku,quantity,price

This is the code I am using to update :

UPDATE ActiveProducts ap INNER JOIN XMLImport xml ON ap.sku = xml.sku
SET
  ap.name = xml.name,
  ap.size = xml.size,
  ap.colour = xml.colour,
  ap.instock = xml.instock,
  ap.quantity = xml.quantity,
  ap.price = xml.price,
  ap.updated = 1

When the two pieces of code are run separately they complete in less than a second each. However when combined like below, the query runs for HOURS !

UPDATE ActiveProducts ap INNER JOIN XMLImport xml ON ap.sku = xml.sku
SET
  ap.name = xml.name,
  ap.size = xml.size,
  ap.colour = xml.colour,
  ap.instock = xml.instock,
  ap.quantity = xml.quantity,
  ap.price = xml.price,
  ap.updated = 1
WHERE ap.sku IN (
SELECT DISTINCT sku FROM (
  SELECT name,size,colour,sku,quantity,price FROM ActiveProducts
  UNION ALL
  SELECT name,size,colour,sku,quantity,price FROM XMLImport
) tbl
GROUP BY name,size,colour,sku,quantity,price
HAVING count(*) = 1
ORDER BY name,size,colour,sku,quantity,price
);

Below is the structure

ActiveProducts.Product_id - PrimeKey
ActiveProducts.name - Index
ActiveProducts.sku - Index

XMLImport.Product_id - PrimeKey
XMLImport.name - Index
XMLImport.sku - Index

Server version: 5.5.44-MariaDB MariaDB Server

Tables: InnoDB


texts: Store the result from a cursor with exec into a temp table without explicitly named columns

texts:
I need to execute a query in each db I have got on my server and the result needs to be stored into a temp table. Due to the query model, I can't explicitly set column names for temp table. I've tried the below query but get "Invalid object name '#tmp'" error (temp table is not available outside the cursor loop). When I change #tmp to global ##tmp I get error "There is already an object named '##tmp' in the database".

Any idea how to bypass it in a simple way without openrowset?

DECLARE
@db VARCHAR(50),
@query VARCHAR(MAX),
@sql VARCHAR(MAX)

SET @query = 'SELECT Col1=1, Col2=2 INTO #tmp'

DECLARE db_cursor CURSOR FOR
SELECT db=name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql =' USE '+@db+'; ' +@query+''
    FETCH NEXT FROM db_cursor INTO @db
EXEC (@sql)
END

CLOSE db_cursor
DEALLOCATE db_cursor

SELECT * FROM #tmp


texts:
Is there a way to only SELECT rows with certain data in a column, without using WHERE?

e.g. If I had this:

SELECT * FROM Users
WHERE town = 'Townsville'

is there a way to implement the WHERE clause into the SELECT statement?

something like

SELECT *, town('Townsville') FROM Users

It's a bizarre question but it's one I've been asked by my peers


texts: Is it now possible to apply Always encrypted to some columns on existing Azure SQL database temporal tables with existing data ? I know it was not possible a few months ago thru SSMS / powershell.


texts:
I'm building a football database. I store data about the position, points, etc of different teams in the past years.

I'm trying to write a query which gets all the teams which have ascended and then descended again. In order to do that, I've the following query.

SELECT DISTINCT team
    FROM Result
    WHERE number=42 AND
        situation='ASCENT'
INTERSECT SELECT DISTINCT team
    FROM Result
    WHERE number=38
        AND situation='DESCENT'
        AND league='Premier league';

The key is that I need to express "temporality"; the year of the ascend has to be previous than the the year of the descend. In my Result table I've a year field, which I could use in order to compare the temporal order, but I don't know how to do it.


texts: Get the list of all Tables, Views, Stored procedures that are not added in a publication for replication

texts: How do I get the list of all Tables, Views, Stored procedures that are NOT added  in a publication for replication?


texts:
I am having a bit of trouble trying to figure out why a query is taking long to execute.
When I check log it seems the log may be either incomplete.

My question is, what is ^M? I see an error related with account_invoice before the Select Statement, is it related with the issue?

I am sure the statement is bad, it takes a lot of time, I did some research with Explain and found out this.

This is the first time I had experienced this issue and it seems to consume lot of cpu (cpu load is more than 10). I currently restart the postgresql server to get things back to working and it is really not the solution I want to implement in future. Any suggestion is much appreciated.

[2017-05-31 13:32:26 ICT] app=x db=y user=z command=SELECTSTATEMENT:  SELECT province.name,district.name,subdistrict.name,district.icc_ship_area_code^M
    FROM province,district,subdistrict,account_invoice,contact^M
    WHERE province.id = province_id^M
    AND district.id = subdistrict.district_id^M

database=# EXPLAIN SELECT

province.name,district.name,subdistrict.name,district.icc_ship_area_code FROM province,district,subdistrict,account_invoice,contact WHERE provi
nce.id = province_id AND district.id = subdistrict.district_id;                                                                                QUERY PLAN
----------------------------------------------------------------------------------------------------
 Hash Join  (cost=7771211730.77..272930003461.72 rows=23422097264624 width=74)
   Hash Cond: (district.province_id = province.id)
   ->  Hash Join  (cost=35.61..301.98 rows=7426 width=55)
         Hash Cond: (subdistrict.district_id = district.id)
         ->  Seq Scan on subdistrict  (cost=0.00..164.26 rows=7426 width=27)
         ->  Hash  (cost=23.05..23.05 rows=1005 width=36)
               ->  Seq Scan on district  (cost=0.00..23.05 rows=1005 width=36)
   ->  Hash  (cost=3075225689.05..3075225689.05 rows=242863114648 width=27)
         ->  Nested Loop  (cost=0.00..3075225689.05 rows=242863114648 width=27)
               ->  Nested Loop  (cost=0.00..39436753.99 rows=3154066424 width=0)
                     ->  Seq Scan on contact  (cost=0.00..3646.99 rows=78899 width=0)
                     ->  Materialize  (cost=0.00..7376.64 rows=39976 width=0)
                           ->  Seq Scan on account_invoice  (cost=0.00..7176.76 rows=39976 width=0)
               ->  Materialize  (cost=0.00..2.16 rows=77 width=27)
                     ->  Seq Scan on province  (cost=0.00..1.77 rows=77 width=27)
(15 rows)


texts:
I am trying to find a way to add business days to any date.
An example would be:

date = '2017-04-28'  (It was a Friday)

date + 2 = '2017-05-02' (It skipped Saturday and Sunday)

Is there a way to do this without custom queries?


texts:
I have a bridge table trajectory_detail that I am supposed to populate with millions of records from the corresponding parent tables. The problem is that every value from parent table trajectory_id is supposed to have at least 3 values of activity_id from parent table activities and the date for all these values must be less than 3 months apart.
            create table medical_trajectory
        (
            traject_id int identity(1,1) not null,
            patient_id int not null,
            PRIMARY KEY(traject_id)

        )

        create table activities (
        activity_id int identity(1,1) not null,
        name varchar(256) not null,
        PRIMARY KEY(activity_id)
    )

        create table trajectory_detail ( -- bridge table
            traject_id int not null,
            activity_id int not null,
            date_performed date not null,
            FOREIGN KEY(traject_id) REFERENCES medical_trajectory(traject_id),
            FOREIGN KEY(activity_id) REFERENCES activities(activity_id)
)

The desired outcome would look like this:

I have somehow managed to resolve this using nested while loops but the performance is just terrible and I am kinda stuck and would really appreciate any help.


texts: SQL Job step run as different sql account

texts:
I'm curious how to set up a SQL job step to be executed as a different SQL login account.  It appears I need to set up a New Proxy Account which requires an existing credential.  When I create a credential my only option is to use a Windows Login credential.

The job I'm attempting to run is below.  There are other additional statements but when I set the job step to run as the SQL login it fails.

insert into [dbo].[TableA]
SELECT
       ss.[Ref_ID]
      ,mm.[studentID]
      ,mm.[studentPersonID]
      ,mm.[studentFirstname]
  FROM [dbo].[TableB] mm
  left outer join [dbo].[TableC] ss on ss.parentPersonID=mm.parentPersonID and mm.studentPersonID = ss.studentPersonID
  where ss.Ref_ID is not null;

When this is run through a SQL Job Step it fails.

Executed as user: an_admin_account. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274).  The step failed.

I'm unclear as to why it's trying to access a remote server when all of these tables exists on the local db.


texts:
I have below situation:

Table1

Field1  Field2
123     test1
234     test2

Table2

Field1  Field2
12345   test2
23245   test3
123945  test6

I want to join these two tables i.e. Table1 and Table2 where Field1 of table1 matches with first three characters of field1 of table2.

So results here should be:

Table1-field1 Table1-field2 Table2-field1 Table2-field2
123           Test1         12345         Test2


texts: Find names of persons who own at least one house in each city in Canada using tuple relational calculus

texts:
I am working on the following exercise:

  Using tuple relational calculus, find names of persons who own at least one house in each city in Canada.

The relevant relation schemas are:

City (city-name, country-name, area, population)
House (hno,#rooms, stno, owner-name)
Street (stno, city-name, length)

Note: Bolded are keys.

This is what I came up with:

{ h.owner-name | House(h) and forAll c (City(c) and c.name="Canada") ->
(Exists s(Street(s) and s.city-name = c.name) and Exists x(House(x) and x.stno = s.stno and x.owner-name = h.owner-name) ) }

How I read this aloud:

"For all Canadian cities, there exists a street with a HOUSE x that has the same owner as the HOUSE h."

Is this correct? I've peen pulling my hair out trying to understand tuple relational calculus.


texts: Which NoSQL for storing images as 2D arrays (BLOBs)

texts:
I have large number of images that I need to access on per-pixel-basis in my app. The images are downloaded from web and are quite large (4k). Update is done several times a day (tousands of images), reading is non-stop.
Currently I use PostGis and store images tiled in raster column type. However, performance is not the best, mainly updates are quite slow (transaction, index update etc).
So I would like to use NoSQL database, since I really dont need SQL.
My workflow is:
Insertion - download image -> split it to tiles of size 64x64 (compress them if necessary with arithetic or huffman coder) -> store tiles in database
Selection - read pixel [x, y] -> locate tile -> retrieve tile -> "unpack" tile in memory -> locate pixel [x, y] -> return value to user
What database should I use for this purpose? I have looked to MongoDB and Cassandra. The later one seems more appropriate for this task.


texts: What is the minimum required compatibility level to use parallel SELECT INTO?

texts: What is the minimum required compatibility level to use parallel SELECT INTO in SQL Server 2014?


texts: Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

texts: I'd like to use InnoDB on the master, for its transactional ability, but MyISAM on the slaves for the full-text search ability. Is this possible?


texts: Is it important to design the database the right way from the beginning?

texts:
I'm a beginner web developer and I use MySQL and sometimes MS SQL Server. However, I don't have that much information about databases, just basic information to be able to build some tables.

Now, what if my site grows and I improved my skills one day in databases and I found that I want to re design my database, will be that possible without affecting the way my application works or without losing any data?


texts:
The BOL and many other sources point out:

  FROM
  Is an optional keyword that can be used between the DELETE keyword and the target
  table_or_view_name, or rowset_function_limited.

I'm used to write DELETE sentences without the FROM. I've made some searches but can't find where the FROM is mandatory. Can anyone point out a situation where it should be mandatory? Or is always optional?


texts:
I'm looking for a way to copy an Oracle user and its data to a new one without dump file.

Google search brings a tip by Steve Karam, which seems to be exactly what I want.
However the page is not accessible to me.

@edited
Thank you for your answers, Justin and Leigh.
I'm considering a simulator of an enterprize system whose database scheme is not designed for it.
In order to apply current situation and to keep the simulated result, I decided to make a cloned database user and execute the simulation on that user.
The simulator will be handled by non-IT person, who could have no knowledge about dump file, so cloning the database must be done internally without any user interaction.
That's the reason why I'm afraid of using export/import tool.


texts: Upgrade/Migration from SQL Server 2008 R2 to SQL Server 2016

texts:
I have a client that is having plans to upgrade/migrate the SQL Server instances from 2008 R2 to 2012. I have provided the details as below. The environments are all Virtual running OS version Windows 2008 r2 enterprise SP1 and uses VMWare.

SQL003 - SQL Server 2008 r2 instance on a WSFC. This SQL Server instance is a publisher with local distributor and subscriber to 2 places SQL007 and SQLDR003. It also has some databases which are both in replication and also part of Mirroring to the DR site (SQLDR003).

SQL007 - SQL Server 2008 R2 instance on a WSFC. This SQL Server instance is a publisher for SQLDR007 and SQL008 and is also a subscriber to SQL003 as noted above as well.

SQL008 - SQL Server 2012 SP1 is a standalone SQL Server instance. I am not too concerned about this instance.

I know that in order for me to move forward with upgrading to SQL Server 2016 the 0S needs to be at least Windows 2012 OS OR later. I am thinking down my head how to achieve this. Is it possible if I can directly upgrade the OS to Windows 2012 or 2016 and then patch SQL Server to 2016 but then how about replication and stuff will this break anything etc or do I need to rebuild a cluster completely and then migrate the objects. It would not be an ideal spot to migrate the databases and all the associated objects based on how the environment is configured. I am in need of some methodology that can save me time and with no changes to the existing hostname etc.


texts:
I think my understanding of precision vs scale might be incorrect as the following example produces values that do not make sense to me. decimal(32, 14) rounds the result to 6 decimal places, while the decimal(18, 14) rounds to 19. My understanding of decimal is decimal(p, [s]), where p is the total number of digits and s is the number of digits after the decimal (i.g., decimal(10, 2) would result in 8 digits to the left of the decimal and 2 digits to the right). Is this not correct?

I created a small example that illustrates the seemingly odd behavior:

--------------------
-- Truncates at pipe
-- 1.043686|655...
--------------------
declare @dVal1 decimal(32, 14) = 10
declare @dVal2 decimal(32, 14) = 9.581419815465469

select @dVal1 Val1, @dVal2 Val2, @dVal1 / @dVal2 CalcResult

----------------
-- Most accurate
----------------
declare @dVal3 decimal(18, 14) = 10
declare @dVal4 decimal(18, 14) = 9.581419815465469

select @dVal3 Val3, @dVal4 Val4, @dVal3 / @dVal4 CalcResult

So on to the question, what is it that I am missing to understand this? The articles and msdn blogs I have read don't seem to provide clarity (at least to my thought process). Can someone explain to me why a higher precision seems to result in a loss of scale?


texts: When a previously-fast SQL query starts running slow, where do I look to find the source of the issue?

texts:
Background

I have a query running against SQL Server 2008 R2 that joins and/or left-joins about 12 different "tables". The database is fairly large with many tables over 50 million rows and about 300 different tables. It's for a large-ish company that has 10 warehouses across the country. All the warehouses read and write to the database. So it's pretty large and pretty busy.

The query I'm having trouble with looks something like this:

select t1.something, t2.something, etc.
from Table1 t1
    inner join Table2 t2 on t1.id = t2.t1id
    left outer join (select * from table 3) t3 on t3.t1id = t1.t1id
    [etc]...
where t1.something = 123

Notice that one of the joins is on a non-correlated sub-query.

The problem is that starting this morning, without any changes (that I or anybody on my team knows of) to the system, the query which usually takes about 2 mins to run, started taking an hour and a half to run -- when it ran at all. The rest of the database is humming along just fine. I've taken this query out of the sproc that it usually runs in and I've run it in SSMS w/ hard-coded parameter variables with the same slowness.

The strangeness is that when I take the non-correlated sub-query and throw it into a temp table, and then use that instead of the sub-query, the query runs fine. Also (and this is the strangest to me) if I add this piece of code to the end of the query, the query runs great:

and t.name like '%'

I've concluded (perhaps incorrectly) from these little experiments that the reason for the slow-down is due to how SQL's cached execution plan is set up -- when the query is a little different, it has to create a new execution plan.

My question is this: When a query that used to run fast suddenly starts running slowly in the middle of the night and nothing else is affected except for this one query, how do I troubleshoot it and how do I keep it from happening in the future? How do I know what SQL is doing internally to make it so slow (if the bad query ran, I could get its execution plan but it won't run -- maybe the expected execution plan would give me something?)? If this issue is with the execution plan, how do I keep SQL from thinking that really crappy execution plans are a good idea?

Also, this is not a problem with parameter sniffing. I've seen that before, and this is not it, since even when I hard-code the varaibles in SSMS, I still get slow performance.


texts: What is a good guideline for sampling for statistics updates

texts:
Scope: A billion record table that is indexed in 10 ways.  Rate of change per day is 1/2 a percent.

Some of the indexes are monotonously increasing (datetime/timestamp), yet the common queries most likely hit the tail end.  I assume statistics need updating frequently for this type, otherwise the recent data becomes unrepresented in the index?

Other indexes are more randomly distributed, e.g. (Customer Key, Datetime).  These can do with less frequent updates, since the statistics are quite representative of the whole.  We can let the index change enough to force auto statistics updates on these, correct?

For both types, is there any benefit from increasing the sampling from 10% to 100%, if the data to be sampled is random and quite representative of the whole?

Looking for best practice with TB data.


texts: SQL Server Deferred Stored Procedure Execution

texts:
I have a stored procedure (e.g. SP_DoSomething) with 2 parameters. Inside the code of this SP there's another SP execution (e.g. SP_DoSomethingElse) with an enormous list of parameters like this:

CREATE PROCEDURE SP_DoSomethingElse
(
    @param1 int,
    ...
    @paramN int
)
AS
BEGIN
    EXEC SP_DoSomethingElse @param1, ... @paramN
END

What I want to achieve is to defer the execution of SP_DoSomethingElse for later execution.

Is there a way to save somewhere (and somehow) the SP execution context (state), including all the parameters and their values?

I prefer not to concatenate as varchar the whole exec.

I found this similar question but is related to replication:

Publishing stored proc execution in transactional replication

If SP_DoSomething is called and there is already a pending SP_DoSomethingElse, no special handling is needed because the calls are not related, and they'll just go into a queue.

This is on SQL Server 2014, but with backward compatibility if possible, no matter if not.


texts: SQL Server Web transaction replication to Azure SQL

texts:
In Features for SQL Server 2016, I see that Transactional replication to Azure is supported for Web edition. Following the instructions leads to Configure Publishing and Distribution

In attempting to create the publication, I get This edition of SQL Server cannot act as a Publisher or Distributor for replication., which is expected as my server is Web edition.

So how do I set up Web edition replication to Azure SQL, given that it is a supported feature?


texts:
When using of database specific features (instead of 3rd party solutions) is justified, especially on the beginning stage of the project?

Example: Postgres has full-text search, which do not support some features of search engines (i.e. facets) but is pretty acceptable for my needs. It requires postgres-specific extensions. But if I use postgres extensions instead of ElasticSearch, I'll bind myself to postgres. Is that a bad practice?

Am I right if I say that all new projects should start with RDBMs (due query flexibility, data normalization) and than migrate to i.e. NoSQL if needed?
If yes, than using db-specific features interfere db migrations in future, and is a bad practice. Also extensions can break db migration tools like Flyway.


texts: Equivalent to BIT_OR function in SQLite?

texts:
I have code that runs against a MySQL database but the unit tests for the code use SQLite.
The issue is one code method uses a SQL query which aggregates using the BIT_OR function of MySQL. Is there an equivalent or a way to replicate its functionality in SQLite?


texts: Postgresql: No data left on drive, how can I wipe it and start over?

texts:
I have a system with a non-critical postgres database that was unmonitored and consumed all available space on its drive. The OS is unaffected so I am free to delete files.

I would like to just delete the files and reinitialize the database to essentially start from scratch. How can this be done?

I'm running postgres 9.1. The config directories are in /etc/postgres but all of the actual postgres data is in /var/lib/postgres, which is mounted in a different volume group.


texts: Oracle SQL Developer: Copy paste tables, with 2 different instances, with different table structure

texts:
Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2, with additional filtration and joining with another table called LKUP.CTL_RWA_VERSION Below is the syntax that I have that is running in DEV.

begin
    for r in (select DISTINCT TABLE_NAME from all_tab_columns where owner = 'DDSHIST' and COLUMN_NAME = 'SNAPSHOT_DT')
      loop
      begin
       execute immediate     'INSERT INTO ||r.table_name|| @DBLINKSIT2
                          select a.*
                          from DDSHIST.||r.table_name|| a
                          INNER JOIN LKUP.CTL_RWA_VERSION b ON a.SNAPSHOT_DT = b.SNAPSHOT_DT and a.DDS_VERSION = b.DDS_VERSION
                          WHERE b.GOLDEN_COPY = 'N'';
       exception when others then null;
      end;
      end loop;
    end;

I put COLUMN_NAME = 'SNAPSHOT_DT' because some of the tables do not contain this column. So the joining condition is both SNAPSHOT_DT are the same, and DDS_VERSION are the same, WHERE golden copy in LKUP table = 'Y'. then loop the script, and insert into @DBLINKSIT2.

But I can't get the script to run and I don't know where I am getting this wrong.

Any help would be appreciated. Thank you.


texts: Clean up space used by internal LOB in Oracle

texts:
I have a table with a CLOB column which is stored as internal LOB. The data in this column becomes obsolete quickly (within matter of days), and is never accessed afterwards.

I wonder whether nullifying this column on regular basis will be enough to let Oracle to reuse the space. E.g. have a scheduler job which would  UPDATE my_table set clob_col =NULL WHERE date_col < sysdate -7 every day.

I'm using Oracle 12c if that matters.


texts:
I have a data like this

AIN     ACCEPT_TYPE DATE1
91957753    A      04-NOV-14 12.50.31 PM
91957753    R      06-NOV-14 11.18.01 AM
919577      A      04-NOV-14 12.50.31 PM
919577      A      05-NOV-14 11.18.01 AM

And look for result like this

AIN     ACCEPT_TYPE_1   ACCEPT_TYPE_2   Date_1                  Date_2
91957753    A                 R             04-NOV-14 12.50.31 PM   06-NOV-14 11.18.01 AM
919577      A                 A             04-NOV-14 12.50.31 PM   05-NOV-14 11.18.01 AM

Is this possible.? Sorry i'm new to oracle .any help will be much helpfull.


texts: Postgres "session variables" kludge on AWS RDS

texts:
I am trying to migrate a PostgreSQL database to AWS RDS.

The problem I ran into was that the database in question makes extensive use of the SET kludge.variable TO 'foo' hack, that is, it uses the customized options syntax for storing runtime session data. (In this case, the DB has a slew of triggers, which seem to be basically reinventing the concept of roles. Rewriting the software isn't really an option at this point, so please bear with me here.)

The main problem with migrating that DB to the AWS cloud is that I couldn't find a way to set a sensible default for the custom param. An RDS database doesn't give you Superuser access required for ALTER SYSTEM SET kludge.param TO 'foo', nor do you get to edit postgresql.conf. (It's possible to edit existing DB parameters via RDS's DB parameter group mechanism, but adding new ones won't work. I tried.)

In addition, trying to get the current_setting(kludge.param) doesn't return anything nice if the option isn't set; instead it causes the statement to fail with an SQL error. Here's what I get when I try to update a table without setting the param in the session:

ERROR:  unrecognized configuration parameter "kludge.param"
CONTEXT:  PL/pgSQL function update_modifier() line 1 at assignment

Furthermore, I couldn't find a way to check for the existence of the parameter without producing the same error.

My current idea is to edit all the trigger functions (although there are many triggers, there aren't ridiculously many trigger functions) to trap error 42P02  undefined_parameter with a WHEN clause, and set a default value for the custom parameter in the error handler.

Given that my only goal seems very simple (inserts and updates work sensibly, with or without setting any custom parameters) and my solution seems to fall in the suspicious category of "clever hacks for enabling other clever hacks", I wanted to check with you guys first:

Does anything I'm saying make any sense at all?
Is there an easier way?
If there isn't, is this approach likely to solve my problem?

Thanks for your time in advance.

(We're running PostgreSQL server version 9.5.10.)